Excel Time Duration Formula

[Jackson]

I'm trying to calculate the time duration in business hours. The
open/close times are in the following format:

7/28/04 8:09 7/28/04 8:18
5/24/04 10:31 6/8/04 12:13
6/17/04 10:58 6/28/04 8:33

The current formula I'm using is:

=(DAY(B3-A3)*8 + HOUR(B3-A3)+MINUTE(B3-A3)/60)

But I need to account for weekends too. I initially thought that I
could shave off 2 days from 7 day periods, but realized that a three
day period could also include a weekend. Is there any way for excel to
determine if a day falls on a Saturday or Sunday? Any other ideas on
how I could account for weekends??


Thanks in advance.

Jackson

 

[CarlosAntenna]

Check out the WEEKDAY and WORKDAY functions.

 

[Myrna Larson]

You don't need DAY. This calculates the day of the month -- what you see when
you look at the calendar.

The straightforward formula B3-A3 calculates the number of days between two
dates.

I believe your formula should look more like this:

=(B3-A3)*8+HOUR(B3)-HOUR(A3)+MINUTE(B3)/60-MINUTE(A3)/60

i.e. you should apply the house and minute functions to the arguments
individually, not to the result of the subtraction.