Thread
:
Excel Time Duration Formula
View Single Post
#
3
Myrna Larson
Posts: n/a
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
Reply With Quote