Excel : Next Specified Day of the Week

Deepanshu Bhalla 1 Comment
Suppose you wish to set up a to-do list utility which notifies repeating events to the same day the following week.

Task : To find out a way to return the date for "Next Sunday", "Next Monday", "Next Tuesday" etc

=TODAY()-WEEKDAY(TODAY())+ROW(A1) + IF(WEEKDAY(TODAY())>ROW(A1),7)
This returns the date for Next Sunday. Paste this formula down to the next 6 cells . You will find the next specified day of the week days.
How this works
1. ROW(A1) returns 1 i.e. row number of A1.

Sunday                  =TODAY()-WEEKDAY(TODAY())+ROW(A1) + IF(WEEKDAY(TODAY())>ROW(A1),7)
Monday                 =TODAY()-WEEKDAY(TODAY())+ROW(A2) + IF(WEEKDAY(TODAY())>ROW(A2),7)
Tuesday                =TODAY()-WEEKDAY(TODAY())+ROW(A3) + IF(WEEKDAY(TODAY())>ROW(A3),7)
Wednesday           =TODAY()-WEEKDAY(TODAY())+ROW(A4) + IF(WEEKDAY(TODAY())>ROW(A4),7)
Thursday              =TODAY()-WEEKDAY(TODAY())+ROW(A5) + IF(WEEKDAY(TODAY())>ROW(A5),7)
Friday                   =TODAY()-WEEKDAY(TODAY())+ROW(A6) + IF(WEEKDAY(TODAY())>ROW(A6),7)
Saturday              =TODAY()-WEEKDAY(TODAY())+ROW(A7) + IF(WEEKDAY(TODAY())>ROW(A7),7)
equivalent to the following formulas :

Sunday               =TODAY()-WEEKDAY(TODAY())+1 + IF(WEEKDAY(TODAY())>1,7)
Monday              =TODAY()-WEEKDAY(TODAY())+2 + IF(WEEKDAY(TODAY())>2,7)
Tuesday             =TODAY()-WEEKDAY(TODAY())+3 + IF(WEEKDAY(TODAY())>3,7)
Wednesday        =TODAY()-WEEKDAY(TODAY())+4 + IF(WEEKDAY(TODAY())>4,7)
Thursday           =TODAY()-WEEKDAY(TODAY())+5 + IF(WEEKDAY(TODAY())>5,7)
Friday                =TODAY()-WEEKDAY(TODAY())+6 + IF(WEEKDAY(TODAY())>6,7)
Saturday           =TODAY()-WEEKDAY(TODAY())+7 + IF(WEEKDAY(TODAY())>7,7)
 2. TODAY returns the current date
 3. WEEKDAY returns a number from 1 to 7 identifying the day of the week of a date.
    Syntax : = WEEKDAY( date, return_type)     By default, return_type is 1
    If return_type is 1 , this returns 1 for Sunday through 7 for Saturday.
    If return_type is 2 , this returns 1 for Monday through 7 for Sunday.
    If return_type is 3 , this returns 0 for Monday through 6 for Sunday.

 4. Formula Evaluation :
     =TODAY()-WEEKDAY(TODAY())+ROW(A1) + IF(WEEKDAY(TODAY())>ROW(A1),7)

 Assuming current date is Monday, January 28 ,2013.
 Next Sunday : 
 41302 - 2 + 1 + IF(2 > 1,7) =41308 i.e. Februrary 03,2013.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

1 Response to "Excel : Next Specified Day of the Week"
Next → ← Prev