Need difference between two dates/times in hours

[ramsdesk]

I am not sure if a similar questions has been posted before... I did
search but couldnt find.

I need the difference between two dates/times field in hours or
minutes.

Eg:

A1 B1
1/4/05 10:00 2/4/05 14:30

The result should be 13.5 hours, considering only 8 hrs per day, only
business days and 8 to 5 workday.

Can you please help me with the right formula.

 

[Biff]

Hi!

What date format are you using? D/M/Y or M/D/Y ?

If you're using D/M/Y then your result should be 7 (or 6 based on your
explanation of an 8 hr day but having a 9 hr time span 8:5) because 2/4/2005
( 2 April 2005) is a Saturday.

If maybe you have the wrong year and it should be 2006, then both dates fall
on a weekend.

If you're using M/D/Y then the result is a lot more than 13.5.

Biff

 

[ramsdesk]

Thanks for pointing it out..

It is in M/D/Y format. Let us have the dates as 4th & 5th of April in
2005. In this case, the hours will be 13.5 totally (7 hrs in 4th & 6.5
hrs in 5th).

 

[Biff]

Or maybe I misunderstood what you want to do?

Biff

 

[ramsdesk]

Any solutions please..

 

[Biff]

If you want to exclude any holidays you'll have to create a list of those
dates in some range of cells and then include that range as the 3 argument
in the Networkdays function:

J1 = 1/1/2005
J2 = 7/4/2005
J3 = 12/24/2005

=NETWORKDAYS(A1,B1,J1:J3.....................

Try this:

A1 = 4/4/2005 10:00 AM
B1 = 4/5/2005 2:30 PM

=(IF(NETWORKDAYS(A1,A1)=1,17/24-MOD(A1,1),0)+IF(NETWORKDAYS(B1,B1)=1,MOD(B1,1)-8/24,0)+IF(NETWORKDAYS(A1+1,B1-1)<1,0,NETWORKDAYS(A1+1,B1-1))*8/24)*24

Format the cell as GENERAL

Returns 13.5

Note: NETWORKDAYS requires the Analysis ToolPak addin be installed.

Biff

 

[daddylonglegs]

Assuming a nine hour day - no lunch break - and your start time and en
time both to be within work hours

=(NETWORKDAYS(A1,B1)-1)*9+(MOD(B1,1)-MOD(A1,1))*24

format as numbe

 

[Biff]

Yeah, that'll work if you don't want any robustness built in.

Biff

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...

 

[daddylonglegs]

Hi Biff

I don’t recognise any lack of robustness in the formula I posted. It
does exactly what I said it would, which is to give the total business
hours between the two time/dates, based on a 9 hour day (with no meal
break) and assuming the start and end times both fall within those
business hours

I think the meal break problem here clouds the issue, I see what you
have attempted to do with the formula you posted but it means that in
some circumstances a later end time/date can result in a shorter time
period returned, e.g.

A1 = 25 Apr 06 09:00, B1 = 27 Apr 06 16:30 > 24.5

A1 = 25 Apr 06 09:00, B1 = 28 Apr 06 08:15 > 24.25

Your formula also gives some strange results in other circumstances

A1 = 25 Apr 06 09:00, B1 = 25 Apr 06 10:00 > 10

Surely this should be 1 hour not 10?

If start/end times outside business hours ARE to be allowed then, again
assuming a nine hour day, from 08:00 to 17:00 and B1 not less than A1,
I’d suggest this formula.

=(NETWORKDAYS(A1,B1,)-1)*9+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1)*24,17,8),17)-IF(NETWORKDAYS(A1,A1),MEDIAN(MOD(A1,1)*24,17,8),8)

 

[Biff]

I don't recognise any lack of robustness in the formula I posted. It

does exactly what I said it would, which is to give the total business
hours between the two time/dates, based on a 9 hour day (with no meal
break) and assuming the start and end times both fall within those
business hours

That's true, but making assumptions usually gets *me* into trouble!

Can we assume that the scope of this application will always meet your
assumed criteria? Maybe, maybe not.

I admit that I tested your formula outside the assumed criteria, used
non-workdays, but that should be something that the formula accounts for
(IMHO):

Date format = M/D/Y

4/1/2005 10:00..........4/2/2005 14:30 returns 4.5

4/2 is a Saturday so the formula should return 7.

4/1/2006 10:00...........4/2/2006 14:30 returns -4.5

Both dates are weekend dates so the formula should return 0

At some point "robustness" turns into overkill and what we offer totally
depends on how we interpret the needs of the poster.

I struggle with this!

Biff

"daddylonglegs" <[email protected]>
wrote in message

 

[daddylonglegs]

Date format = M/D/Y

4/1/2005 10:00..........4/2/2005 14:30 returns 4.5

4/2 is a Saturday so the formula should return 7.

4/1/2006 10:00...........4/2/2006 14:30 returns -4.5

Both dates are weekend dates so the formula should return 0

At some point "robustness" turns into overkill and what we offer
totally
depends on how we interpret the needs of the poster.

I struggle with this!

Hello again Biff,

Thanks for your reply.

My experience of situations where this sort of formula is asked for is
that sometimes the start and end dates are never outside office hours,
e.g. when they are project start and end times, but sometimes they are,
e.g. server downtime type queries.

When I don't know which situation applies my approach is usually to
offer the simpler formula, giving it's limitations, then to suggest the
more complex formula if that is what's needed. Of course for the
examples you give above, the more complex formula is required....

=(NETWORKDAYS(A1,B1,)-1)*9+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1)*24,17,
8),17)-IF(NETWORKDAYS(A1,A1),MEDIAN(MOD(A1,1)*24,17,8),8)

This will give the correct results as above and also correct results in
all other situations where A1 is not greater than B1.

I believe the formula you posted will give the correct results in the
examples you give but not in many other cases, e.g.

Date format = M/D/Y

4/1/2005 05:00..........4/2/2005 14:30 returns 12

should return 9

....now, if there needs to be a meal break that might need a further
modification.....