# 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

 =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.