Subtracting dates to get hours... but I want to skip weekends
 

In column A I have start times (DD/MM/YY HH/MM/SS) and in column B I
have the stop times. I am measuring the hours from the difference of
these columns for each row, in column C.

However, I would like to exclude weekends from the subtraction. I.e
Friday 5 pm is the start time, Monday 8 am is the stop time, then the
difference would be 15 hours instead of 63 hours.

I know how to convert the fractions returned from the difference, into
hours/minutes/days, and I also know how to convert dates to "day of
week", so unless you guys know of a good idea I was just going to sort
the file by day of week, and start manually deducting 48 hours from the
ones that cross over.

Hoping for an easier way though, 7,000 records and I would like to do
this regularly....

thanks


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=473428

I assume you have =B1-A1 in C1. If you are not going across a weekend,
WEEKDAY() will increase from A1 to B1. So use:

=IF(WEEKDAY(B1)=WEEKDAY(A1),B1-A1,B1-A1-48)

On my sheet I have to use 2 instead of 48 because my units are days.
--
Gary''s Student


"shadestreet" wrote:


In column A I have start times (DD/MM/YY HH/MM/SS) and in column B I
have the stop times. I am measuring the hours from the difference of
these columns for each row, in column C.

However, I would like to exclude weekends from the subtraction. I.e
Friday 5 pm is the start time, Monday 8 am is the stop time, then the
difference would be 15 hours instead of 63 hours.

I know how to convert the fractions returned from the difference, into
hours/minutes/days, and I also know how to convert dates to "day of
week", so unless you guys know of a good idea I was just going to sort
the file by day of week, and start manually deducting 48 hours from the
ones that cross over.

Hoping for an easier way though, 7,000 records and I would like to do
this regularly....

thanks


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=473428

brilliant!

Thanks


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=473428