Calculating a Rolling Date

[andrewc]

Hi,

I am trying to create a formula that looks at the current date and returns a date a year back. If the current date is the last week day in the month I would like the start date to be the last week day of the same month a year back (for example, the current date is 28 November2008 - the last week day of the month - I would like the start date to be the last week day of November 2007).

And if the current date is not a month-end I would like the formula to return a start date that is the same day and month of a year ago (for example, if the current day is 1st November 2008, I would like the formula to return a start date of 1st November 2007), unless the day a year ago (the "1st" in my example) falls on a weekend in which case I would like the start date to be on the preceding Friday.

I hope that this makes sense and that someone can suggest a solution.

Thanks!!!

[Bob Phillips]

Try

=IF(MONTH(A16)<>MONTH(WORKDAY(A16,1)),WORKDAY(DATE(YEAR(A16)-1,MONTH(A16)+1,0),-1),WORKDAY(DATE(YEAR(A16)-1,MONTH(A16),DAY(A16)),-1))

[daddylonglegs]

Try this formula, where your original date is in A1

=WORKDAY(IF(MONTH(A1)<>MONTH(WORKDAY(A1,1)),EOMONTH(A1,-12),EDATE(A1,-12))+1,-1)

Note: this uses Analysis ToolPak functions WORKDAY, EOMONTH and EDATE so you need the toolpak installed. If formula gives an error try installing with

Tools> add-ins > tick "Analysis ToolPak"