Calculate hours between 2 dates/times

**cords** · 12-09-2010, 04:15 AM

There are 2 dates written as, for example, in one cell, say 12/2/2010 3:30 PM and another cell, say 12/7/2010 10:20 PM. The calculation that is needed is the following:

1) Total hours excluding weekends if the start and end times are not BOTH on the weekend. Also, just to be clear, a weekend is from midnight Sat to midnight Mon.

2) If either date, or both dates, falls on a weekend, then the weekend should be included.

3) The dates can span over weeks. I'm not sure what to do if the start and end dates are on the weekends here. It doesn't matter much for spans > 9 days if the weekends count or not. Your choice.

4) The output should be in a 'number' format that can be graphed; so, a 48 hour span should be graphed as the number 48.

5) The calculation can be accurate to within 3 hours; if that helps.

If this cannot be 'easily' handled, what is your closest approximation to the above?

If desired, I can give examples of all of the above scenarios in an excel spreadsheet.

Many thanks in advance!!!!!

**DonkeyOte** · 12-09-2010, 05:50 AM

Originally Posted by cords

If desired, I can give examples of all of the above scenarios in an excel spreadsheet.

That would be good - detail expected results for each example of course.

**squiggler47** · 12-09-2010, 06:42 AM

Possibly :-

=(IF(OR(MOD(A1,7)<2,MOD(B1,7)<2),(ROUNDUP(A1,0)-(ROUNDDOWN(B1,0)-"00:00:01")),NETWORKDAYS(ROUNDUP(A1,0),ROUNDDOWN(B1,0)-"00:00:01"))+MOD(B1-A1,1))*24

if either day is a weekend then include weekends, otherwise only include working days

it should be accurate to 1 second

**daddylonglegs** · 12-09-2010, 07:25 AM

Hello Darren,

when I use that formula using cords' example dates I get a result of 54.83. I think that's 24 hours too little, the total hours between them is 126.83, with 2 weekend days so shouldn't the result be 78.83?

This version should give the correct hours, I think

=IF(NETWORKDAYS(A1,A1)+NETWORKDAYS(B1,B1)<2,B1-A1,NETWORKDAYS(A1,B1)-1+MOD(B1,1)-MOD(A1,1))*24

where start date/time is in A1 and end date.time in B1

format result cell as number

It counts all hours between A1 and B1 if either of the dates is at the weekend, otherwise it excludes weekends