There are many circumstances in life where we want to determine the coming day. We are always eager to wait for Saturday. Hence, we want to show the coming Saturday date. We want to go to a blockbuster movie on Wednesday, hence we want coming Wednesday date. All these are fairly easy by Windows Date / Time Calendar or Calendars on mobile or through other calendars in day to day life. Excel not needed. But in business scenarios, Excel is the tool which you would most likely use.
Now, consider Excel and business scenarios where you would really need the help of this article. Your shift starts on Monday and you want to display date for coming Monday.
Also, suppose you opened your Excel sheet on Monday itself. Now, you are in a dilemma whether to show today's date itself or next Monday's date. For example, you opened your sheet on 26-May-14 and you need to decide whether to show 26-May-14 as coming Monday's date or 2-Jun-14 as coming Monday's date.
I had responded to a question on Microsoft Excel Community Forum and many persons had posted the response. I particularly found the answer of Ashish Mathur very interesting. That discussion can be read here. There are many ways to achieve the objectives but this particular formula, I found to be very elegant.
The question was related to coming Thursday's date and utilizing Ashish Mathur's answer, I decided to make formulas for all coming days and also to handle the scenarios whether show today's date or coming day if the Sheet is opened on that particular day itself.
Below are the formulas
Scenario 1 – If the sheet is opened that day, show that day itself.
Sunday =TODAY()+CHOOSE(WEEKDAY(TODAY()),0,6,5,4,3,2,1)
Monday =TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,6,5,4,3,2)
Tuesday =TODAY()+CHOOSE(WEEKDAY(TODAY()),2,1,0,6,5,4,3)
Wednesday =TODAY()+CHOOSE(WEEKDAY(TODAY()),3,2,1,0,6,5,4)
Thursday =TODAY()+CHOOSE(WEEKDAY(TODAY()),4,3,2,1,0,6,5)
Friday =TODAY()+CHOOSE(WEEKDAY(TODAY()),5,4,3,2,1,0,6)
Saturday =TODAY()+CHOOSE(WEEKDAY(TODAY()),6,5,4,3,2,1,0)
Scenario 2 – If the sheet is opened that day, show that next coming day.
Sunday =TODAY()+CHOOSE(WEEKDAY(TODAY()),7,6,5,4,3,2,1)
Monday =TODAY()+CHOOSE(WEEKDAY(TODAY()),1,7,6,5,4,3,2)
Tuesday =TODAY()+CHOOSE(WEEKDAY(TODAY()),2,1,7,6,5,4,3)
Wednesday =TODAY()+CHOOSE(WEEKDAY(TODAY()),3,2,1,7,6,5,4)
Thursday =TODAY()+CHOOSE(WEEKDAY(TODAY()),4,3,2,1,7,6,5)
Friday =TODAY()+CHOOSE(WEEKDAY(TODAY()),5,4,3,2,1,7,6)
Saturday =TODAY()+CHOOSE(WEEKDAY(TODAY()),6,5,4,3,2,1,7)
Note – In Scenario 2 set of formulas, we are just replacing 0 with 7 in Scenario 1 formulas.
How to test – To test the formulas, copy all the formulas in an excel sheet and replace today() with a cell. Now, in that cell you can give dates and test for various dates.
Another variation of the above formula is using INDEX
Scenario 1 – If the sheet is opened that day, show that day itself.
Sunday =TODAY()+INDEX({0,6,5,4,3,2,1},WEEKDAY(TODAY()))
Monday =TODAY()+INDEX({1,0,6,5,4,3,2},WEEKDAY(TODAY()))
Tuesday =TODAY()+INDEX({2,1,0,6,5,4,3},WEEKDAY(TODAY()))
Wednesday =TODAY()+INDEX({3,2,1,0,6,5,4},WEEKDAY(TODAY()))
Thursday =TODAY()+INDEX({4,3,2,1,0,6,5},WEEKDAY(TODAY()))
Friday =TODAY()+INDEX({5,4,3,2,1,0,6},WEEKDAY(TODAY()))
Saturday =TODAY()+INDEX({6,5,4,3,2,1,0},WEEKDAY(TODAY()))
Scenario 2 – If the sheet is opened that day, show that next coming day.
Sunday =TODAY()+INDEX({7,6,5,4,3,2,1},WEEKDAY(TODAY()))
Monday =TODAY()+INDEX({1,7,6,5,4,3,2},WEEKDAY(TODAY()))
Tuesday =TODAY()+INDEX({2,1,7,6,5,4,3},WEEKDAY(TODAY()))
Wednesday =TODAY()+INDEX({3,2,1,7,6,5,4},WEEKDAY(TODAY()))
Thursday =TODAY()+INDEX({4,3,2,1,7,6,5},WEEKDAY(TODAY()))
Friday =TODAY()+INDEX({5,4,3,2,1,7,6},WEEKDAY(TODAY()))
Saturday =TODAY()+INDEX({6,5,4,3,2,1,7},WEEKDAY(TODAY()))
You can make formulas parameter driven also and that is the very reason why I have made the formulas through INDEX also.
Suppose you give a value "Sun" without quotes in cell E1. Now you want coming Sunday date to be reflected. Similarly, for "Mon" to "Sat" also. Then your formula becomes –
Scenario 1
=TODAY()+INDEX(INDEX({0,6,5,4,3,2,1;1,0,6,5,4,3,2;2,1,0,6,5,4,3;
3,2,1,0,6,5,4;4,3,2,1,0,6,5;5,4,3,2,1,0,6;6,5,4,3,2,1,0},
QUOTIENT(IF(E1="","",SEARCH(E1,"SunMonTueWedThuFriSat")),3)+1),
WEEKDAY(TODAY()))
Scenario 2
=TODAY()+INDEX(INDEX({7,6,5,4,3,2,1;1,7,6,5,4,3,2;2,1,7,6,5,4,3;3,
2,1,7,6,5,4;4,3,2,1,7,6,5;5,4,3,2,1,7,6;6,5,4,3,2,1,7},
QUOTIENT(IF(E1="","",SEARCH(E1,"SunMonTueWedThuFriSat")),3)+1),
WEEKDAY(TODAY()))
What these two set of formulas are doing – Basically they just populate the right array. So, for Tuesday for Scenario 1, you have following formula –
Tuesday =TODAY()+INDEX({2,1,0,6,5,4,3},WEEKDAY(TODAY()))
Now if you give Tue for parameter driven formula, the array {2,1,0,6,5,4,3} will be picked up and formula will become =TODAY()+INDEX({2,1,0,6,5,4,3},WEEKDAY(TODAY()))
Depending upon your needs, you can do some error handling built in like if in place of Sun, the person puts in Son etc….In this case recommended method is to provide a list box through validation list where person can choose a valid day like Sun, Mon…Sat only.