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.

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

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

Next → ← Prev