Excel : Next Specified Day of the Week

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

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 :

 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
About Author:

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

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

Next → ← Prev
Love this Post? Spread the Word!