Dates - countdown from last day of previous month

Okay so I need help with a way of identifying what the previous month was to a given date, then in sequential columns counting down a day at a time for 65 days (don't ask!)

For example: in cell A1 I have a reference date which is, for the sake of argument, 15/04/2012 (dd/mm/yyyy format).

I need a way to tell Excel to identify the previous month, then start the date on the LAST DAY of that month, then for each further column subtract one more day.

So if A1 is 15/04/2012 then in B1 I would have 31/03/2012, C1 would be 30/03/2012, D1 would be 29/03/2012 etc

It can't be a function that relies on add-ins etc but all PCs it will run on will have Excel 2007 or above.

Thanks!

good on you for finding a very nice solution.

i recently upgraded to 2010, and i don't exactly remember if EOMONTH is a part of the 2007 Analysis Toolpak Add-in or not; but you can give it a shot, if you want.

if date is in A1, put this in B1, and drag-fill right until you see "-".

Please Login or Register  to view this content.

1300