Excel Time Duration Formula
 

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

Check out the WEEKDAY and WORKDAY functions.


"Jackson" wrote in message
om...
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

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.

On 4 Nov 2004 09:51:23 -0800, (Jackson) wrote:

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