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
>

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

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

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)