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

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" wrote in
message ...

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


--
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" wrote in message
...
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" wrote
in message ...

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


--
ramsdesk
------------------------------------------------------------------------
ramsdesk's Profile: http://www.excelforum.com/member.php...o&userid=15705
View this thread: http://www.excelforum.com/showthread...hreadid=535417

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" wrote in
message ...

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535417

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

Biff

"daddylonglegs"
wrote in message
news:daddylonglegs.26siga_1145918103.7933@excelfor um-nospam.com...

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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535417

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"
wrote in message
news:daddylonglegs.26tcba_1145956801.9069@excelfor um-nospam.com...

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

Biff Wrote:

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535417