Formula to calculate elapsed time between certain dates and times

Hi
I am trying to calculate the elspased time between certain dates and times
with a result in total hours.

The catch is that I only want the formula to inlcude times during business
hours (Monday to Friday, 8:30am - 5:30pm)

E.g (17/12/2004 10:07) - (3/02/2005 15:22) = ???

Is this possible with excel?

Many thanks

Hi!

Do you want to exclude any holidays that fall on regular
business days?

A1 = 17/12/2004 10:07
B1 = 3/02/2005 15:22

=SUM(17.5/24-MOD(A1,INT(A1)),MOD(B1,INT(B1))-8.5/24)+
(NETWORKDAYS(A1,B1)-2)*9/24

Format the cell as [h]:mm.

If you want to exclude holidays you need to make a list of
holidays and then include a reference to that list as a
3rd argument in the NETWORKDAYS function.

Example:

Say you list holidays in the range C1:C10.

Include that range in the NETWORKDAYS function:

.....NETWORKDAYS(A1,B1,C1:C10)....

Biff

>-----Original Message-----
>Hi
>I am trying to calculate the elspased time between
certain dates and times
>with a result in total hours.
>
>The catch is that I only want the formula to inlcude
times during business
>hours (Monday to Friday, 8:30am - 5:30pm)
>
>E.g (17/12/2004 10:07) - (3/02/2005 15:22) = ???
>
>Is this possible with excel?
>
>Many thanks
>
>.
>

Hmmm...

That can be slightly shortened to:

=SUM(18/24-(A1-INT(A1)),(B1-INT(B1))-8/24)+(NETWORKDAYS
(A1,B1)-2)*9/24

Biff

>-----Original Message-----
>Hi!
>
>Do you want to exclude any holidays that fall on regular
>business days?
>
>A1 = 17/12/2004 10:07
>B1 = 3/02/2005 15:22
>
>=SUM(17.5/24-MOD(A1,INT(A1)),MOD(B1,INT(B1))-8.5/24)+
>(NETWORKDAYS(A1,B1)-2)*9/24
>
>Format the cell as [h]:mm.
>
>If you want to exclude holidays you need to make a list
of
>holidays and then include a reference to that list as a
>3rd argument in the NETWORKDAYS function.
>
>Example:
>
>Say you list holidays in the range C1:C10.
>
>Include that range in the NETWORKDAYS function:
>
>.....NETWORKDAYS(A1,B1,C1:C10)....
>
>Biff
>
>>-----Original Message-----
>>Hi
>>I am trying to calculate the elspased time between
>certain dates and times
>>with a result in total hours.
>>
>>The catch is that I only want the formula to inlcude
>times during business
>>hours (Monday to Friday, 8:30am - 5:30pm)
>>
>>E.g (17/12/2004 10:07) - (3/02/2005 15:22) = ???
>>
>>Is this possible with excel?
>>
>>Many thanks
>>
>>.
>>
>.
>

I'm a bit more concerned about user input errors. I think you need a check
that the starting and ending dates are in fact workdays, i.e,

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


On Tue, 22 Mar 2005 23:29:08 -0800, "Biff" <[email protected]> wrote:

>Hmmm...
>
>That can be slightly shortened to:
>
>=SUM(18/24-(A1-INT(A1)),(B1-INT(B1))-8/24)+(NETWORKDAYS
>(A1,B1)-2)*9/24
>
>Biff
>
>>-----Original Message-----
>>Hi!
>>
>>Do you want to exclude any holidays that fall on regular
>>business days?
>>
>>A1 = 17/12/2004 10:07
>>B1 = 3/02/2005 15:22
>>
>>=SUM(17.5/24-MOD(A1,INT(A1)),MOD(B1,INT(B1))-8.5/24)+
>>(NETWORKDAYS(A1,B1)-2)*9/24
>>
>>Format the cell as [h]:mm.
>>
>>If you want to exclude holidays you need to make a list
>of
>>holidays and then include a reference to that list as a
>>3rd argument in the NETWORKDAYS function.
>>
>>Example:
>>
>>Say you list holidays in the range C1:C10.
>>
>>Include that range in the NETWORKDAYS function:
>>
>>.....NETWORKDAYS(A1,B1,C1:C10)....
>>
>>Biff
>>
>>>-----Original Message-----
>>>Hi
>>>I am trying to calculate the elspased time between
>>certain dates and times
>>>with a result in total hours.
>>>
>>>The catch is that I only want the formula to inlcude
>>times during business
>>>hours (Monday to Friday, 8:30am - 5:30pm)
>>>
>>>E.g (17/12/2004 10:07) - (3/02/2005 15:22) = ???
>>>
>>>Is this possible with excel?
>>>
>>>Many thanks
>>>
>>>.
>>>
>>.
>>

Hi Myrna!

>I think you need a check that the starting and ending
>dates are in fact workdays

Yes, I agree that would be a good thing to do!

I came up with this more robust version but I only call
Networkdays once:

=SUM((18/24-MOD(A1,1))*(WEEKDAY(A1,2)<6),MOD(A15,1)-8/24*
(WEEKDAY(A15,2)<6)+(NETWORKDAYS(A1,A15)-SUM(WEEKDAY(A1,2)
<6,WEEKDAY(A15,2)<6))*10/24)

I'm using a time range of 8:00 to 18:00 with start
date/time in A1, end date/time in A15 for testing.

It's longer and kind of ugly but it works. I do like the
simplicity of your formula, however, multiple calls to
Networkdays results in #NAME? if you are troubleshooting
and using Evaluate Formula.

Biff

>-----Original Message-----
>I'm a bit more concerned about user input errors. I think
you need a check
>that the starting and ending dates are in fact workdays,
i.e,
>
>=IF(NETWORKDAYS(A1,A1)=1,18/24-MOD(A1,1),0)
> +IF(NETWORKDAYS(B1,B1)=1,MOD(B1,1)-8/24,0)
> +NETWORKDAYS(A1+1,B1-1)*9/24
>
>
>On Tue, 22 Mar 2005 23:29:08 -0800, "Biff"
<[email protected]> wrote:
>
>>Hmmm...
>>
>>That can be slightly shortened to:
>>
>>=SUM(18/24-(A1-INT(A1)),(B1-INT(B1))-8/24)+(NETWORKDAYS
>>(A1,B1)-2)*9/24
>>
>>Biff
>>
>>>-----Original Message-----
>>>Hi!
>>>
>>>Do you want to exclude any holidays that fall on
regular
>>>business days?
>>>
>>>A1 = 17/12/2004 10:07
>>>B1 = 3/02/2005 15:22
>>>
>>>=SUM(17.5/24-MOD(A1,INT(A1)),MOD(B1,INT(B1))-8.5/24)+
>>>(NETWORKDAYS(A1,B1)-2)*9/24
>>>
>>>Format the cell as [h]:mm.
>>>
>>>If you want to exclude holidays you need to make a list
>>of
>>>holidays and then include a reference to that list as a
>>>3rd argument in the NETWORKDAYS function.
>>>
>>>Example:
>>>
>>>Say you list holidays in the range C1:C10.
>>>
>>>Include that range in the NETWORKDAYS function:
>>>
>>>.....NETWORKDAYS(A1,B1,C1:C10)....
>>>
>>>Biff
>>>
>>>>-----Original Message-----
>>>>Hi
>>>>I am trying to calculate the elspased time between
>>>certain dates and times
>>>>with a result in total hours.
>>>>
>>>>The catch is that I only want the formula to inlcude
>>>times during business
>>>>hours (Monday to Friday, 8:30am - 5:30pm)
>>>>
>>>>E.g (17/12/2004 10:07) - (3/02/2005 15:22) = ???
>>>>
>>>>Is this possible with excel?
>>>>
>>>>Many thanks
>>>>
>>>>.
>>>>
>>>.
>>>
>
>.
>

On Wed, 23 Mar 2005 20:44:15 -0800, "Biff" <[email protected]> wrote:

>I came up with this more robust version but I only call
>Networkdays once:

But that won't work if the user includes a list of holidays and one or the
other dates is a holiday. (I know, I'm paranoid about user errors!)

>multiple calls to
>Networkdays results in #NAME? if you are troubleshooting
>and using Evaluate Formula.

Sounds like a bug in the Evaluate Format routine. I evaluate parts of a
formula by highlighting in the formula bar and pressing F9. I don't get any
errors.