Days & Hours B/t Two Dates & Times

**Grock258** · 10-23-2008, 05:16 PM

Hey,

I can’t figure out why this formula is not working. I am trying to alter it a little, but I still think it should work. Here is what I am trying to do. I am looking to calculate the time difference between two work projects, but exclude time when the office is closed. So someone starts a project at 2pm and finish 10am the next day it will show a result of 4 hours because the office closes at 5.

Here is the formula. It is the time formula from cpearson.com. http://www.cpearson.com/excel/DateTimeWS.htm

=IF(AND(INT(D1)=INT(D2),NOT(ISNA(MATCH(INT(D1),HolidayList,0)))),0,ABS(IF(INT(D1)=INT(D2),ROUND(24*(D2-D1),2),(24*(DayEnd-DayStart)*(MAX(NETWORKDAYS(D1+1,D2-1,HolidayList),0)+INT(24*(((D2-INT(D2))-(D1-INT(D1)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+MOD(ROUND(((24*(D2-INT(D2)))-24*DayStart)+(24*DayEnd-(24*(D1-INT(D1)))),2),ROUND((24*(DayEnd-DayStart)),2))))))

D1 is the start time of the first project. (ex: 7/4/2008 7:40:35)*
D2 is the start of the second project & I am using that as the end time of the first project. (7/4/2008 13:01:16)*
DayEnd is the time the office closes.
DayStart is the time the office opens.
*Dates in dd/mm/yyyy format.

All my results come up as zeros, regardless of how I format the cells. I would think that this formula would be able to count down to the minute.

Also, has anyone ever included break times using this formula? I would like to add that in, but I haven’t really focused on it yet.

Any suggestions, ideas will be much appreciated.

**Richard Buttrey** · 10-23-2008, 07:37 PM

Hi,

It's perhaps stating the obvious, but have you got the Analysis Tool Pack add in installed so that the NETWORKDAYS() function can be used, as Chip mentions?

In addition does your working day straddle midnight. He also says that would not give the right result, and do you have the 'HolidayList' set up?

HTH

**Grock258** · 10-24-2008, 09:46 AM

I do have the analysis pack installed and my holiday list setup. My end of day is 11:59pm. I think this is the issue. I think I'll have try something different being I have too many entries to exclude the 11pm data.

Thanks for the help.

**daddylonglegs** · 10-24-2008, 06:44 PM

Chip's formula should work OK but you don't really need that huge formula, this would suffice

=(NETWORKDAYS(D1,D2,HolidayList)-1)*(DayEnd-Daystart)+MOD(D2,1)-MOD(D1,1)

result cell should be formatted as [h]:mm

Both this formula and Chip's only work correctly where both D1 and D2 are within work hours, you can cater for start and end outside work hours too but you need a more complex formula

See this thread for more. The formula posted by Kolacube [which is mine from another thread] is a version of the above catering for lunch breaks, my reply contains a further amendment to cater for start and finish time to be outside work hours