Calculating Project Dates And Times
Apr 30, 2014calculating project completion % based on Project start date and end dates .
View 5 Replies
ADVERTISEMENT
Calculating Lead Time Between Two Dates / Times - Excluding Holidays And Weekend
Apr 18, 2013Calculating Lead time (in hours) between two dates/times, excluding holidays and weekend
Start Time
End Time
Lead Time
12/26/2012 15:50
1/2/2013 12:38:00
??????????
Sum The Total Hours By Project Between Two Dates
May 30, 2007I need to sum the total hours by project between two dates. There can be multiple projects and the two dates can vary. So...
In column A are the project # (say A2 001, A3 001, A4 002, A5 003)
In column B is Yes or No for each project (contract Signed?)
In Column C through Z, row 1, are dates (shows the Monday of each week)
In Column C through Z, row 2-5 (which corresponds to the projects 001, 001, 002, and 003 above) are the number of hours worked that week.
On a separate tab (lets call it MonthTab) is the start and end date for each month (Jan through Dec)
On a separate tab I want to SUM the hours by Month for each project (so for project 001 would need to go across multiple rows), that has a Yes in column two. Therefore the hours summed For January will be taken from MonthTab and be between StartJan and EndJan.
Calculating Between Two Times
Nov 24, 2009I had an excellent response last time I posted here, this time I’m stuck again with a new formula. I’m trying to calculated amounts between different times, but keep tying my self in knots with complicated IF formulas.
Is there an easier way to work out hours worked between 2 times, but too complicated things further I need three separate amounts so I’m guessing I’ll need three separate formulas
Hours between 00:00 – 06:00
Hours between 06:00 – 19:00
& hours between 19:00 – 00:00
An example could be, 05:00 – 20:00 should be 1,13,1
Calculating Times On A Timesheet?
Aug 3, 2008we work 8hrs 30mns for 4 days 7hrs 30mns for 1 day this is monday to friday
using Excel i need a formula that will add the following:-
a1 8.30
a2 8.30
a3 8.30
a4 8.30
a5 7.30
total 41.30
Calculating Difference Between Two Times
Mar 29, 2007I am trying to calculate the difference between a scheduled start time and an actual start time. If the actual start time is greater than the scheduled start time a negative time (hh:mm:ss) should be returned. instead the cell is populated with #############....
I have tried to reformat using the custom formats, but the only options in excel 97 are for either standard numerics or £ (these return a minus figure).
I've had a look at the time functions but could not see a suitable one.
I'm sure there is a simple format solution to this.
Use Conditional Formatting For Calendar That Highlights Workweeks Of Project Dates?
Aug 3, 2014I think this should work but it doesn't.
=AND($B4>=D$2,$C4<=D$3)
Should look like this
Excel Conditional Formatting.png
Calculating Using Times And Different Rates For Bookings?
Oct 14, 2013I am trying to create a booking calculation sheet for facilities. I want it to create something like a quote for customers. So when booking facilities, the hourly rate changes after 6pm. What I want to do is to put in a start time and a finish time and it calculate how many hours are before 6pm and charge them at $12 per hour, as well as how many hours after 6pm and charge them $18 per hour. I have tried a few things but they don't seem to work. I'm struggling with the logic of it really. This is mainly because the start time may or may not be before 6pm, as too the finish time.
View 6 Replies View RelatedCalculating Turn Around Time Between Two Times
Mar 12, 2009i want to calculate the TAT between two times. the TAT target is <= 2 hours. i used the following formula
a1 has 3/13/2009 (received date)
b1 has 7:08 AM (received time)
c1 has 3/13/2009 (completed date)
d1 has 9:08 AM (completed time)
e1 has TAT formula :- '=IF((D1-B1)*1440<=120,"Met TAT","Not Met TAT")
however this formula does not work in the following conditions.
In these conditions, it is considered that TAT is met.
1. When the difference in time is <=2 hours .... for TAT calculation, on working days and working hours are taken into consideration.
To illustrate.
Day begins : 8:00 AM
Day ends : 4:00 PM
If job is received at 3:30 PM and completed the next working day by 9:30 AM, then it is considered TAT is met.
calculation = 4:00 PM - 3:30 PM = half hour + next day's 9:30 AM - 8:00 AM = 1.5 hours, therefore, total working hours used to complete the job is within the agreed TAT.
If job is received and completed on non working days and during non working hours, it is considered TAT met.
If job is received almost at the end of the day, say, 3.30 PM and job is completed at 8:00 PM same day, then it is considered TAT met, rationale, only half an hour of working hours used to complete the job.
Calculating Pick Up Times During SLAs
Nov 11, 2013I need to calculate the amount of time it takes to pick up a document whilst taking into account:
- If the document was submitted before 9am or after 5pm, I need the pick up time to count from only 9am
- If the document was submitted on a different date to the pick up date, I need to factor in the days inbetween into the calculation (eg, if the document was submitted on 1st Jan at 9am, but wasn't picked up until 10am on 3rd Jan, the time to pick up would be 17 hours in total)
- If the document was picked up outside of the SLA hours, I only need to count up to the SLA starting/ending (eg, if something was submitted at 4.50pm and picked up at 8am the next day, the pick up time would be 10mins)
I've tried multiple ways of doing this and as soon as I think I have it cracked, I get an error. Below is a sample of the table I am working with:
Submit date
Submit time
Pick up date
Pick up time
Time to pick up
3/11/13
09:50:42
5/11/13
12:03:14
??????
6/11/13
11:13:54
6/11/13
14:14:31
?????
Calculating Times: Minutes And Hours
Sep 25, 2006i need to get a formula that will calucate hours and min. its for how many hours the employee has not worked. some of them would be strait hours some would be just min there is no way to tell.
example
lates 2 hours
anp(absent no pay) 12 hours
sicks 55.5 hours
no calls
early outs 21 min
(this is just an example if it were real this person would be fired)
i know this adds up to 69.85 hours but i can't fuiger out a way to get it to calucate in excel. i know i could have it all changed to min and then devied by 60 to get the hours but how do i get it to read what is mins and whats hours?
IF/VLOOKUP (set Up A Spreadsheet Which Automatically Calculates The Start And End Dates Of Project Tasks)
Feb 1, 2009I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
View 3 Replies View RelatedCalculating Difference In Times As Hours And Minutes
Aug 10, 2009I need to calculate the difference between a start time and end time in hours and minutes.
Start
01/07/2008 11:40
End
01/08/2008 19:28
Start and End columns are formatted as 'Custom' m/d/yyyy h:mm.
I'm not sure what formula to write to calculate the hours and minutes between the two times. Everything I've tried doesn't count over 24 hours. Also what do I format the result cell as?
Calculating Difference Between Two Times Through A Column Of Data
Aug 21, 2013It will be easier to explain in an example:
A B
Time (hh:mm:ss) Digital
1 10:03:00 0
2 10:03:01 0
3 10:03:02 1
4 10:03:03 1
5 10:03:04 1
6 10:03:05 0
[code]....
From the data above I am searching for the duration of when the digital column says '1', i.e my function = A5-A3 which would output 2 secs or 00:00:03 & likewise A9-A8.
The problem I have is that the digital signal is staggered and does not always have the same frequency. I have over 6848 lines of time to check so to do this manually would take me all day.
Automatically Calculating Shift Start Times
Sep 20, 2013I'm currently working on a rota for which I would like the worksheet to automatically tell me how many staff members I have beginning their shift before 9am and those finishing after 7pm. I currently have this working via a very crude set of IF statements for each staff member for each day of the week, returning 1 if true and 0 if false. Then I have a sum statement at the bottom of each day. Is there a much tidier and simpler way for me to calculate this?
I've attached the worksheet. A quick note is in Q52.
Calculating Order Entry Times With A Multiplier
Sep 16, 2009B2: =SUMPRODUCT(--(INT('Order Entry'!$B$2:$B$37)=Summary!$A2) )
C2: =SUMPRODUCT( (INT('Order Entry'!$B$2:$B$37)=Summary!$A2) * 'Order Entry'!$A$2:$A$37) / Summary!B2
D2: =MIN(IF(INT('Order Entry'!$B$2:$B$37)=Summary!$A2, 'Order Entry'!$A$2:$A$37))
E2: =MAX(IF(INT('Order Entry'!$B$2:$B$37)=Summary!$A2, 'Order Entry'!$A$2:$A$37))
The overall picture is an order tracking sheet that has start times in column B, end times in column C, number of jobs in an order number in column D and the processing time in column A. On the Summary sheet I have dates listed for each workday.
Next to these dates I am wanting a formula that will traverse through column B of the Custom and Order Entry sheets and provide the number of orders and average processing time that match this. There can be multiple jobs per order number so it needs to order the Sumproduct by column D for each.
Calculating Start And Finish Times For A Process
Feb 22, 2010I have a number of processes that I would like to calculate the finish times of.
For example, Process A may take 18 hours to complete, Process B may take 28 hours to complete and Process C may take 125 hours to complete etc
However these processes only run during certain times, i.e. 9am to 5pm.
Ideally I would like to setup a spreadsheet that when given the start time and process time calculates the (date and) time the process will finish.
I can get it to work on a 24 hour day but I've been tearing my hair out trying to take out the none-working part of the day (the 5pm to 9am).
Calculating Project Costs With The Costs Different Each Month?
Jul 28, 2014I'm dealing with Projects that have an X amount of Units used in a certain period of time. The amount of units are evenly spread among the time the project runs. The problem is that many projects start in 1 month and end in another.
To clarify, the code in my head is like this:
Total Units in project: 80
project starting on 12 jan, project ran 19 days in Jan.
Project ending on 14 feb, project ran 14 days in Feb.
Cost of a unit in Jan = 200
Ofcourse, the code needs to be smart, since the data will be filled in to a table, so the code has to change automatic every time a new project is entered. I cant use pivots 'cause of a standard format sheet.I have the costs per month in a raw data sheet. Start date and end dates in different cells next to each other.
Calculating Total Time For Individual With Multiple Start / End Times
Feb 12, 2014I am trying to come up with a formula that calculates total time someone has worked in a day. The scenario is an individual will work at a home and start working with an individual. Their start/end times look like this in a pivot:
Min Start Max Start Min End Max End
Location A+Counselor A 8:56 AM4:01 PM 1:11 PM 7:00 PM
Location A+Counselor B 12:00 AM 8:00 PM 6:00 AM 11:59 PM
Location B:Counselor C 7:00 AM 12:00 PM 2:00 PM 4:00 PM
Location C+Counselor D 8:00 AM 8:00 AM 4:00 PM 4:00 PM
Some people work split shifts while others work a straight shift. The formula I created was this:
=IF(OR(B9=C9,E9=D9,D9=C9),E9-B9,IF(D9>C9,((E9-D9)+(C9-B9)),IF(C9>D9,((D9-B9)+E9-C9),"New Formula Needed")))*24
(I use a pivot table to show max min for start and end times)
This works great except for the individuals that have multiple punches during the same time frame. The one scenario I am having trouble solving for is when someone punches in more than once during their shift displaying. This occurs when a counselor starts a shift working with one person but then adds another person mid shift. An example of this could be:
Location A+Counselor E Min Start Max Start Min End Max End
Consumer 1 1:00 PM 1:00 PM 8:30 PM 8:30 PM Total Time: 7.5
Consumer 2 12:00 PM 12:00 PM 2:35 PM 2:35 Pm Total Time: 2.6
Pivot says that they worked a total of 10.1 because it is grabbing the max and mins and calculating. The actual total time worked is 8.5 hours in reality.
The raw data comes in like so:
Location Counselor Consumer Start Time End Time
A A A 1:00 PM 8:30 PM
A A B 12:00 PM 2:35 PM
A A C 12:00 PM 5:00 PM
Is this solvable with a formula?
Generating List Of Project Parts Based On Project Duration And Time Window
Aug 4, 2014I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
Consolidate 4 Excel Project Lists (Workbooks) To New Master Project List Using VBA
Sep 5, 2013My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
Get Excel To Open MS Project File And Then Run Project Macro On It
Jul 30, 2013I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END
[code].....
Protect VBA Project Or Lock Project For Viewing By VBA
Apr 27, 2007I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.
View 9 Replies View RelatedSum Times Between Dates
Dec 9, 2006I have a file with two sheets.
Sheet1 Data are as follows :
Column A : Date (Data is : 31/01/2006,01/02/2006 ...... )
Column B : Name (Data is : ABC , XYZ, ABC .......)
Column C : Time (Format is : HH:MM) (02:00 , 03:00 .....)
Sheet2 Data are as follows :
A1 : Start Date and B1 : End Date
A2 : Name and B2 : Sum of Time
A3 : ABC and B3 : (i Want Result in this cell)
Result is :
I want Result in B3 cell : Name(ABC) of Time sum from sheet1 Column C between Start date(A1) to End Date (B1).
Calculating Time Between Two Dates?
May 15, 2012I need to calculate an SLA.
I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:
dd/mm/yy hh:00
I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.
I need a macro that compares the two and works out the following:
< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"
Calculating Two Dates In A Spreadsheet
Mar 3, 2009to know the number of hours between A5 and J5 of my spreadsheet - working day is 8.30am to 5.30pm also need excluding weekends and bank holidays in the uk. I need the result to appeer in K5. the date and time format i have is: 2009-02-04 - 11:56:39 in column a5 and 2009-02-16 - 14:23:45 in column J5
I have over 100 of these calculations to do per month and it takes me ages manually.
Calculating Dates From Text
Mar 31, 2009I have two columns. The first column contains cells with multiple date values in text format (YYYY/MM/DD) in each cell. The other I was planning to be Calculated cells that I thought would compare the string of text (possibly converted to numbers) and determine which is the next in sequence after today's date. As there may be numbers in the past I cannot just use the first item.
The cell of text looks like this:2009-03-27 00:00:00
2009-04-10 00:00:00
2009-04-29 00:00:00
2009-05-13 00:00:00
2009-05-27 00:00:00
The calculated cell (based on today's date) should output:2009-04-10 00:00:00
Problem: I've been unable to figure out a calculation that will accomplish this. The idea I had was to examine the text in pieces and compare them numerically to "today" (as a number instead of a date).
I'm not very adept with Excel functions yet. I've started with this: "=(MID(A2,2,10))" but all it does is return the first item. I also tried using a Replace function to get rid of the dashes and also one that would return the dates as a decimal. But while I can get the numbers to change format, I don't know how to do the compare...
Calculating Dates In Each Month
Sep 27, 2006I need to calcuate how many times a date( any date for the month) aprears in the list. it needs to be calucated by each indivual month form a list that will span over a 12month period.
the problem i'm having is how do i get it to only read the month that i want?
Calculating Dates And Numbers
Apr 3, 2007I am starting a project that will involve finding how old people are using the difference of two years. Cell A1 will contain the function that automatically obtains the current year from the PC clock (or a manual input). Cells B1...B6 will contain text (names). Cells C1...C6 will contain the month of their birth. Cell D1...D6 will contain the day of their birth. And cells E1...E6 will contain the year of their birth. Cells F1...F6 will have the formula to calculate the difference between A1 and its respective cell column E.
The only functions I know are =(TODAY) which does not give me just the year. Also, I do not know how I would be able to do the calculation [e.i., =(TODAY) - 1988].