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.
equivalent to the following formulas :
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.
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)
|
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)
|
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)
|
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.
How do I do this for every second weekday?
ReplyDelete