I may be making this more complicated than it needs to be but I can't seem to figure it out. The goal of this spreadsheet is for General Managers of a restaurant to give feedback about food deliveries. These deliveries usually come on Monday, Thursday, and Saturday, but could for a list of reasons come on any day of the week. I want to automatically populate as much information as possible without requiring GMs to remember and enter dates.
A1 is =TODAY()
A2:A5 is a drop down where the GM selects the day of the week the delivery arrived. i.e. MON, TUE, WED, etc. I want the corresponding date of the past week to auto-populate in this cell (or even the adjacent cell if necessary) once the day is selected. For example, if today is 3/8/2014, when MON is selected in A2, I want the cell to automatically add "3/3/2014," making the entire cell read "MON 3/3/2014."
So if said GM is filling in this spreadsheet on a Saturday, and I want to display the date of the previous Monday, I need to find the day of the week of A1 (WEEKDAY function), and make A2 = A1-5.
If today is a Friday, it would be A2=A1-4, and so on for the 7 days of the week.
Then I would need to do all of this for if a Tuesday is selected in the drop down box, meaning IF(Saturday)Then A2=A1-4, etc...
I was trying to string together IF statements like this:
=IF(WEEKDAY(TODAY()=7),A1-5), IF(WEEKDAY(TODAY()=6),A1-4)
It works when I do only the first IF statement, but when I add another it returns #VALUE. I thought that excel would find the first true value and stop evaluating.
I hope someone can help, and thank you in advance!
Bookmarks