I need a formula that will provide a workday date if the duration in days is
subtracted from a given end date. I found variations of what I need on the
internet, but I do not know how to pull it together. Attempts have failed.

Maybe using =workday() from the Analysis toolpak (in xl2003 and below) would do
what you want.

See Excel's help for more information.

JeannetteH wrote:

I need a formula that will provide a workday date if the duration in days is
subtracted from a given end date. I found variations of what I need on the
internet, but I do not know how to pull it together. Attempts have failed.

--

Dave Peterson

Thanks Dave,

I did put my brain to work and resolved it using the workday function.

Basically what I was trying to accomplish is a simulation of Project. I
needed to take an end date and calculate backwards to a start date. This is
what I developed for anyone else who is a newbie like me.

Start date formula: =IF(C3=1,F3,WORKDAY(F3,-C3+1,Holidays))

c3 = duration
if (duration is 1, then start date = end date [because we only want to
calculate 1 8 hour day], else determine workday using end date-F3, subtract
(- duration + 1),account for holidays) [+ 1 accounts for the 8 hour day] .

Holidays: using a calendar I wrote the holiday dates applicable within the
time period I was calculating on a discrete section of the worksheet and
named the group "Holidays" then I could refer to that group in the function.

Initial end date is input as =date(2007,10,5) then the first start formula
calculates from this date and the rest of the fields are copied upward.

End date Formula: =IF(WEEKDAY(E5)=2,E5-3,E5-1)
This formula is the end date just above the initial end date and is copied
upward.
it looks at the start date for the level below and determines if it falls on
a Monday, if it does it needs to deduct Sat and Sun. So if Weekday(Previous
Start date = Mon, then subtract 2 from initial end date, else subtract 1)

JeannetteH

"Dave Peterson" wrote:

Maybe using =workday() from the Analysis toolpak (in xl2003 and below) would do
what you want.

See Excel's help for more information.

JeannetteH wrote:

I need a formula that will provide a workday date if the duration in days is
subtracted from a given end date. I found variations of what I need on the
internet, but I do not know how to pull it together. Attempts have failed.

--

Dave Peterson