days/date formula

[kins]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
<o> </o>
I need a formula whereby I have a date and I want to take one working day, 2 working and so on, from my initial date. For example –
I have a date of Monday 23<SUP>rd</SUP> Jan 2012, in the next cell I want it to add to days (or 1 or 3) to this day to give me the day i.e. 1 working day before 23/01/2012 would give me the 20/01/2012, 3 working days would give me 18/08/2012.
<o> </o>
Have I explained myself OK?
<o> </o>
Thanks in advance,
<o> </o>

Kins.

 

[Richard Schollar]

Hi

Assuming xl2003 or earlier, you need to iunstall the Analysis Toolpak via Tools>Addins. Then (and in xl2007 or higher) you can use the WORKDAY function:

=WORKDAY(YourDateCellHere,DaysToAddOrTakeAway,OptionalHolidayList)

eg

=WORKDAY(A1,-1)

 

[Jonmo1]

Try

=WORKDAY(A1,-1,E1:E20)

Where E1:E20 is an optional range containing holidays to be excluded.

This requires the analysis toolpack from Tools - Addins

Hope that helps.

 

[kins]

thanks for the above, richard. i knew it would be simple, and i wasn't a million miles away.

 

[kins]

thanks jonmo.

 

[asking]

Hi kins,

Have you tried doing a fill series by ~ type = date, date unit = weekday, step -1?