Formula to calculate elapsed time between certain dates and times

[Guest]

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

 

[Biff]

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

 

[Biff]

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

.

.

 

[Myrna Larson]

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

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

.

.

 

[Biff]

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

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

.

.

.

 

[Myrna Larson]

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.

 

[Biff]

Hi Myrna!

But that won't work if the user includes a list of
holidays and one or the other dates is a holiday.

Yes, I know. That's why I asked the OP if they wanted to
include holidays when I replied. But the OP hasn't
responded and it would be easy to include checks for
holidays.

See this about the #NAME? issue:

http://tinyurl.com/5x3cj

Biff

-----Original Message-----