Formula for business hours

[Fran]

Please help! I need a formula that will calculate the
amount of time - based on an 8:00 to 5:00 business day.

2/10/03 9:00 am (These dates will be in their own cells)
2/11/03 11:30 am

I need a formula to equate to 11 hrs 30 min.

THANK YOU!!!

[J.E. McGimpsey]

One way, assuming you want to exclude weekends:

=NETWORKDAYS(A1,A2)-2+TIME(17,0,0)-(A1-INT(A1))
+(A2-INT(A2))-TIME(8,0,0)

In article <001301c2e020$7344cfe0$a501...@phx.gbl>, Fran

[Fran]

THANK YOU THANK YOU for responding! Please don't think me
ungrateful but can you help me again? I think I see what
this formula is doing but I can't get it to work.

Do I need to have my A1 adn A2 cells formatted in a
specific way?

>.
>

[Ken Wright]

Hi Fran, JEs formula works fine for me:-

Cell A1 = 10/02/2003 09:00
Cell A2 = 11/02/2003 11:30

Cell C1 contains JEs formula, and is formatted as [hh]:mm to ensure it
captures any time periods greater than 24 hours

Returns 11:30, BUT if you are going to use thge result of this in any other
calculations you will probably need to multiply it by 24. Excel stores
times as fractional days with a day being equal to 1. 11:30 hours is 0.48
days, and it is just the format that makes it say 11:30. If you had a
dollar field elsewhere multiplying your 11:30 as is by $10/Hr you would get
$4.80 as opposed to the $115.00 you would want. You need to multiply the
11:30 cell by 24 in whatever formula you use it in.

--
Regards
Ken....................... Win XP / XL2K & XLXP

----------------------------------------------------------------------------
Public Service Request - It is very much appreciated
in text-only groups if you don't attach files - Thanks
----------------------------------------------------------------------------

"Fran" <tmfr...@usfamily.net> wrote in message
news:027e01c2e028$21120d20$3001...@phx.gbl...

[J.E. McGimpsey]

As long as A1 and A2 are dates, not text, you won't have to do any
special formatting. The cell with the formula can be formatted as

d "days" hh:mm "hours"

or, if you just want hours, use

=9/24*(NETWORKDAYS(A1,A2)-2)+TIME(17,0,0)-(A1-INT(A1))
+(A2-INT(A2))-TIME(8,0,0)

and format as [hh:mm]

In article <027e01c2e028$21120d20$3001...@phx.gbl>, Fran