software

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: www.excelforum.com/member.php...oamp;userid=15705
View this thread: www.excelforum.com/showthread...hreadid=535417Hi!

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

quot;ramsdeskquot; gt; wrote in
message ...
gt;
gt; I am not sure if a similar questions has been posted before... I did
gt; search but couldnt find.
gt;
gt; I need the difference between two dates/times field in hours or
gt; minutes.
gt;
gt; Eg:
gt;
gt; A1 B1
gt; 1/4/05 10:00 2/4/05 14:30
gt;
gt; The result should be 13.5 hours, considering only 8 hrs per day, only
gt; business days and 8 to 5 workday.
gt;
gt; Can you please help me with the right formula.
gt;
gt;
gt; --
gt; ramsdesk
gt; ------------------------------------------------------------------------
gt; ramsdesk's Profile:
gt; www.excelforum.com/member.php...oamp;userid=15705
gt; View this thread: www.excelforum.com/showthread...hreadid=535417
gt;

Thanks for pointing it out..

It is in M/D/Y format. Let us have the dates as 4th amp; 5th of April in
2005. In this case, the hours will be 13.5 totally (7 hrs in 4th amp; 6.5
hrs in 5th).--
ramsdesk
------------------------------------------------------------------------
ramsdesk's Profile: www.excelforum.com/member.php...oamp;userid=15705
View this thread: www.excelforum.com/showthread...hreadid=535417Or maybe I misunderstood what you want to do?

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; What date format are you using? D/M/Y or M/D/Y ?
gt;
gt; If you're using D/M/Y then your result should be 7 (or 6 based on your
gt; explanation of an 8 hr day but having a 9 hr time span 8:5) because
gt; 2/4/2005 ( 2 April 2005) is a Saturday.
gt;
gt; If maybe you have the wrong year and it should be 2006, then both dates
gt; fall on a weekend.
gt;
gt; If you're using M/D/Y then the result is a lot more than 13.5.
gt;
gt; Biff
gt;
gt; quot;ramsdeskquot; gt; wrote
gt; in message ...
gt;gt;
gt;gt; I am not sure if a similar questions has been posted before... I did
gt;gt; search but couldnt find.
gt;gt;
gt;gt; I need the difference between two dates/times field in hours or
gt;gt; minutes.
gt;gt;
gt;gt; Eg:
gt;gt;
gt;gt; A1 B1
gt;gt; 1/4/05 10:00 2/4/05 14:30
gt;gt;
gt;gt; The result should be 13.5 hours, considering only 8 hrs per day, only
gt;gt; business days and 8 to 5 workday.
gt;gt;
gt;gt; Can you please help me with the right formula.
gt;gt;
gt;gt;
gt;gt; --
gt;gt; ramsdesk
gt;gt; ------------------------------------------------------------------------
gt;gt; ramsdesk's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=15705
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=535417
gt;gt;
gt;
gt;

Any solutions please..--
ramsdesk
------------------------------------------------------------------------
ramsdesk's Profile: www.excelforum.com/member.php...oamp;userid=15705
View this thread: www.excelforum.com/showthread...hreadid=535417If 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)lt;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

quot;ramsdeskquot; gt; wrote in
message ...
gt;
gt; Thanks for pointing it out..
gt;
gt; It is in M/D/Y format. Let us have the dates as 4th amp; 5th of April in
gt; 2005. In this case, the hours will be 13.5 totally (7 hrs in 4th amp; 6.5
gt; hrs in 5th).
gt;
gt;
gt; --
gt; ramsdesk
gt; ------------------------------------------------------------------------
gt; ramsdesk's Profile:
gt; www.excelforum.com/member.php...oamp;userid=15705
gt; View this thread: www.excelforum.com/showthread...hreadid=535417
gt;

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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=535417Yeah, that'll work if you don't want any robustness built in.

Biff

quot;daddylonglegsquot; gt;
wrote in message
news:daddylonglegs.26siga_1145918103.7933@excelfor um-nospam.com...
gt;
gt; Assuming a nine hour day - no lunch break - and your start time and end
gt; time both to be within work hours
gt;
gt; =(NETWORKDAYS(A1,B1)-1)*9 (MOD(B1,1)-MOD(A1,1))*24
gt;
gt; format as number
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=535417
gt;

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 gt; 24.5

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

Your formula also gives some strange results in other circumstances

A1 = 25 Apr 06 09:00, B1 = 25 Apr 06 10:00 gt; 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=535417gt;I don't recognise any lack of robustness in the formula I posted. It
gt;does exactly what I said it would, which is to give the total business
gt;hours between the two time/dates, based on a 9 hour day (with no meal
gt;break) and assuming the start and end times both fall within those
gt;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 quot;robustnessquot; turns into overkill and what we offer totally
depends on how we interpret the needs of the poster.

I struggle with this!

Biff

quot;daddylonglegsquot; gt;
wrote in message
news:daddylonglegs.26tcba_1145956801.9069@excelfor um-nospam.com...
gt;
gt; Hi Biff
gt;
gt; I don't recognise any lack of robustness in the formula I posted. It
gt; does exactly what I said it would, which is to give the total business
gt; hours between the two time/dates, based on a 9 hour day (with no meal
gt; break) and assuming the start and end times both fall within those
gt; business hours
gt;
gt; I think the meal break problem here clouds the issue, I see what you
gt; have attempted to do with the formula you posted but it means that in
gt; some circumstances a later end time/date can result in a shorter time
gt; period returned, e.g.
gt;
gt; A1 = 25 Apr 06 09:00, B1 = 27 Apr 06 16:30 gt; 24.5
gt;
gt; A1 = 25 Apr 06 09:00, B1 = 28 Apr 06 08:15 gt; 24.25
gt;
gt; Your formula also gives some strange results in other circumstances
gt;
gt; A1 = 25 Apr 06 09:00, B1 = 25 Apr 06 10:00 gt; 10
gt;
gt; Surely this should be 1 hour not 10?
gt;
gt; If start/end times outside business hours ARE to be allowed then, again
gt; assuming a nine hour day, from 08:00 to 17:00 and B1 not less than A1,
gt; I'd suggest this formula.
gt;
gt; =(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)
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=535417
gt;