How can this formula be adjusted to show following week\'s info from Saturday on
ID: 3565573 • Letter: H
Question
How can this formula be adjusted to show following week's info from Saturday on and not waiting till Sunday?
I have a workbook that opens up when I start Windows and it's great. Since I rarely work on the weekends, I didn't see a particular issue till this past Saturday.
We have weekly deadlines on Fridays. So the cell C2 has a date format in it of ddd.mmm.dd.yyyy, and this formula:
=CHOOSE(WEEKDAY(TODAY()-WEEKDAY(TODAY())+6),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(TODAY()-WEEKDAY(TODAY())+6,".mmm.dd.yyyy")
It works great on weekdays; for example, the text in C1 says "This week's Friday is:" and C2 showed last week every day as "Fr.Oct.17.2014". Perfect!
But on Saturday when I came in, the worksheet still showed Fr.Oct.17.2014. I believe work weeks turn over on Sundays in Excel (?).
Glad to say I didn't have the opportunity to test yesterday as I come in <lol>, but I imagine yesterday it would have said Fr.Oct.24.2014, so Sunday is probably okay (?). Saturday should ideally do that, too.
How can the above formula be adjusted please?
Thank you!!
Explanation / Answer
It seems you always want to return the next Friday, unless Today is a friday in which case return today.
Try:
=TEXT(TODAY()+7-WEEKDAY(TODAY()-6),"""Fr.""mmm.dd.yyyy")
Since the date will always be a Friday, there is no need for the CHOOSE(WEEKDAY(TODAY()-WEEKDAY(TODAY())+6),"Sn","Mn","Tu","Wd","Th","Fr","Sa") portion of the formula.
You could also use the simpler:
=TODAY()+7-WEEKDAY(TODAY()-6)
with a custom format of "Fr."mm.dd.yyyy
If you did that things might get confusing should someone edit the formula in that cell in the future -- of course, if you did that, you would be able to use that date in future calculations; but you should definitely protect that cell from being changed.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.