Need difference between two dates/times in hours

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> ramsdesk
> ------------------------------------------------------------------------
> ramsdesk's Profile:
> http://www.excelforum.com/member.php...o&userid=15705
> View this thread: http://www.excelforum.com/showthread...hreadid=535417
>

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).

Or maybe I misunderstood what you want to do?

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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.
>>
>>
>> --
>> ramsdesk
>> ------------------------------------------------------------------------
>> ramsdesk's Profile:
>> http://www.excelforum.com/member.php...o&userid=15705
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=535417
>>
>
>

Any solutions please..

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

"ramsdesk" <[email protected]> wrote in
message news:[email protected]...
>
> 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).
>
>
> --
> ramsdesk
> ------------------------------------------------------------------------
> ramsdesk's Profile:
> http://www.excelforum.com/member.php...o&userid=15705
> View this thread: http://www.excelforum.com/showthread...hreadid=535417
>

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

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

format as number

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

Biff

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> Assuming a nine hour day - no lunch break - and your start time and end
> time both to be within work hours
>
> =(NETWORKDAYS(A1,B1)-1)*9+(MOD(B1,1)-MOD(A1,1))*24
>
> format as number
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=535417
>

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)

>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
news:[email protected]...
>
> 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)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=535417
>

Originally Posted by Biff

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.....

Originally Posted by daddylonglegs

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.....

Formula is working.

Need some following changes in the above below formula

1. From the above formula if i want to one holiday then how to calculate.
2. From the above formula result is coming like 0.75 (actual time is 45mins) insted of this can we get the reslut in time fomula like 00:45 or 1:20(1 hr 20mins).

Please help on this.

Regards
Kasi