Another question about time and date

[Guest]

Hi-
I would like to know if the formula I found from Biff's post will work for
my situation:
I need to find the elapsed time between 2 dates which includes business
hours only,
e.g. 1/12/06 3:35 PM to 1/17/06 8:23 AM
The Formula that Biff wrote is
=SUM(18/24-(A1-INT(A1)),(B1-INT(B1))-8/24)+(NETWORKDAYS (A1,B1)-2)*9/24
Will this formula work for the hours of 8-5p, and exclude holidays?

**Also, I need to calculate total business hours but also include non
business hours:
1/12/06 5:55 PM to 1/16/06 11:44 PM. Is there a separate formula for that as
well?

thanks for your help!

 

[Biff]

Try this:

=(NETWORKDAYS(A1,B1,holidays)-1)*9+(MOD(B1,1)-MOD(A1,1))*24

Returns 19.8

If you want the result in time:

=((NETWORKDAYS(A1,B1,holidays)-1)*9+(MOD(B1,1)-MOD(A1,1))*24)/24

Format as [h]:mm

Returns 19:48

To exclude holiday dates make a list of those dates and include that range
reference as the third argument of the NETWORKDAYS function. For example:

C1 = 1/1/2006
C2 = 1/31/2006

NETWORKDAYS(A1,B1,C1:C2)

All this assumes that both the start date/time...end date/time will always
be on a business day and in business hours.

**Also, I need to calculate total business hours but also include non
business hours:

Non-business hours would be: total hours minus business hours

Biff

 

[Guest]

Great, thanks- this is helpful.
--I'm not sure how it would look if I wanted to calculate non-business hours
in the formula- can you show me an example of where I would add that in?
thanks again,
Erin

Try this:

=(NETWORKDAYS(A1,B1,holidays)-1)*9+(MOD(B1,1)-MOD(A1,1))*24

Returns 19.8

If you want the result in time:

=((NETWORKDAYS(A1,B1,holidays)-1)*9+(MOD(B1,1)-MOD(A1,1))*24)/24

Format as [h]:mm

Returns 19:48

To exclude holiday dates make a list of those dates and include that range
reference as the third argument of the NETWORKDAYS function. For example:

C1 = 1/1/2006
C2 = 1/31/2006

NETWORKDAYS(A1,B1,C1:C2)

All this assumes that both the start date/time...end date/time will always
be on a business day and in business hours.

**Also, I need to calculate total business hours but also include non
business hours:

Non-business hours would be: total hours minus business hours

Biff

Hi-
I would like to know if the formula I found from Biff's post will work for
my situation:
I need to find the elapsed time between 2 dates which includes business
hours only,
e.g. 1/12/06 3:35 PM to 1/17/06 8:23 AM
The Formula that Biff wrote is
=SUM(18/24-(A1-INT(A1)),(B1-INT(B1))-8/24)+(NETWORKDAYS (A1,B1)-2)*9/24
Will this formula work for the hours of 8-5p, and exclude holidays?

**Also, I need to calculate total business hours but also include non
business hours:
1/12/06 5:55 PM to 1/16/06 11:44 PM. Is there a separate formula for that
as
well?

thanks for your help!