I've got a database of dates for advertising. I want to keep the ads running on the same day, but it will obviously be a different date. E.g. Wednesday 12 January 2011 must be changed to Wednesday 11 January 2012. How do I do this in Excel? What formula do I use?

Adding 364 to each date should give you the same day of the week in 2012 as you have in 2011. I tested it with the WEEKDAY function and it works for all dates except for New Years Day.

1/1/2011 + 364 = 12/31/2011 The day of the week is the same (7 = Saturday) but 364 days later is still 2011.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Just be careful; blindly adding XXX days will break on a leap year.

I don't disagree, but in this case, where we are trying to find Dates to match the day of the week from one year to the next, adding 364 doesn't really "break" during a Leap Year. Adding 364 will indeed return a date that is the same day of the week year after year after (leap) year.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll