Hoping for a code that auto-populates a date based on other fields

[DianeDennis]

Hi!

I have a cell that I enter the week ending date into, as dd. Then further into the document are seven fields, one for each day (date) of the aforementioned week, also entered as dd. 

Is it possible to have the seven individual date fields auto-populate, working from the date entered in the "week ending date" field?

For example:

"Sunday's date" will auto-populate by subtracting 6 from the date entered in the "Week Ending Date" field.

"Monday's date" will auto-populate by subtracting 5 from the date entered in the "Week Ending Date" field.

"Tuesday's date" will auto-populate by subtracting 4 from the date entered in the "Week Ending Date" field.

and so on...

At the same time though, it needs to work in as many earlier versions of Excel as possible (I've found that some of the more involved formulas don't work in earlier versions of Excel).

I hope that makes sense. :)

Thank you so much!!

Diane

[#2]

can you show a sample data of what you want as i didnt understand fully

[#3 · Accepted solution]

@DianeDennis Irrespective of how the "last day of the week" is formatted, you can just point at it in a formula and deduct 1, 2, 3 etc. to return the earlier dates.

Dates in Excel are in fact sequential numbers that start counting from January 1, 1900. 

Let's say the "last day of the week" is in A1. In its simplest form, just enter =A1-1 to return the previous day's date, and format it to your liking. Repeat this for the other days, wherever you have them in your sheet.