How do I subtract 8 business hours from a date/time value?

[drew]

I am trying to subtract 8 hours from a date/time. What I need to do is to
capture that lead time using only weekdays, and working hours of 8am-5pm.

For example:

Date/Time
6/23/2008 9:18AM (Monday)

Subtracting 8 hours from this scenario would give me
6/20/2008 10:18AM (friday)

 

[Lars-Åke Aspelin]

I am trying to subtract 8 hours from a date/time. What I need to do is to
capture that lead time using only weekdays, and working hours of 8am-5pm.

For example:

Date/Time
6/23/2008 9:18AM (Monday)

Subtracting 8 hours from this scenario would give me
6/20/2008 10:18AM (friday)

If you by weekdays simply mean Mondays through Fridays, you can try
this:

=A1-8/24-15/24*(HOUR(A1)<16)-2*AND(HOUR(A1)<16,WEEKDAY(A1,2)=1)

Hope this helps/ Lars-Åke

 

[drew]

This does help. Thanks!!
One more request. If I want to add another column that subtracts business
hours from the column we just created... where in the formula would I input
the hours I want to subtract? And , would it still be business hours?

Thanks again!!

 

[Lars-Åke Aspelin]

Refering to the formula below

=A1-8/24-15/24*(HOUR(A1)<16)-2*AND(HOUR(A1)<16,WEEKDAY(A1,2)=1)

the 8 in 8/24 is the number of business hours to subtract
the 15 is the number of "non working hours"
the 16 (in two places) is the start of working hour plus the number of
business hours to subtract.

So to generalise the formula bit:

=A1-A4/24-(24-(A3-A2))/24*(HOUR(A1)<(A2+A4))-2*AND(HOUR(A1)<(A2+A4),WEEKDAY(A1,2)=1)

where
the start of work hours (8) is in A2
the end of work hours (17) is in A3
the business hours to subtract is in A4
(A2, A3, A4 should not be formatted as TIME, just as NUMBER.)

Hope this helps / Lars-Åke

 

[drew]

Lars, I tried this formula with the values in A2-A4 as you suggested, but it
is not quite there yet. Maybe an even easier way to go, is instead of
subtracting 8 hours, can we subtract 12?

A1: 6/23/2008 9:00 AM
a2: 8
a3: 17
a4: 12

The formula gave me 6/20/2008 6:00 AM as the value, but in actuality is
should be 6/19/2008 3:00 PM.

Thanks!

 

[Lars-Åke Aspelin]

Sorry drew, the formula I provided was not as general as I stated.
It only works if the starting time is on the same weekday or on the
weekday before the end time.
That is alway the case if the business hours to subtract is less than
or equal to one working day.

Lars-Åke