Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to calculate the first occurrence in the next year of the weekday of the date in cell A1. For instance, if the date in cell A1 happens to fall on a Thursday, calculate the first Thursday of the next year; if the date in cell A1 falls on a Monday, then calculate the first Monday of the next year, and so on.

How can I do this?

share|improve this question

migrated from webapps.stackexchange.com Mar 25 at 8:04

This question came from our site for power users of web applications.

1  
First monday, in first week of january next year? Or first monday in the given week from the date in cell A1? –  mk117 Mar 25 at 8:08
    
@mk117 Seems pretty clear to me - the first occurrence of the given weekday next year, whichever week and month it falls to (though of course it'll be in the first or second week of January). –  Indrek Mar 25 at 9:13

3 Answers 3

In my french version of Excel:

=DATE(ANNEE(A1)+1;1;1)+MOD(7+JOURSEM(A1)-JOURSEM(DATE(ANNEE(A1)+1;1;1));7)

Which seems to be in the english version:

=DATE(YEAR(A1)+1,1,1)+MOD(7+WEEKDAY(A1)-WEEKDAY(DATE(YEAR(A1)+1,1,1)),7)
share|improve this answer

I like @CmPi's answer. Here is another solution:

=DATE( 
   YEAR(DATE(YEAR(B1)+1,1,1)),  
   1,  
   MOD(7+WEEKDAY(B1)-WEEKDAY(DATE(YEAR(B1)+1,1,1)),7)+1
)

Here is a sanity check:

(Date format is set to Long, US Locale)

sanity check screenshot from excel

In @CmPi's example he finds the first date of the next year and adds the appropriate number of days from the first to the instance of the same weekday. In this method I just set the next day within the same date.

share|improve this answer

Try this

=CHOOSE(WEEKDAY(DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

CHOOSE: Selects the day from the weekday array (DATE: Sun,Mon,Tue,etc) WEEKDAY: Gets the day of the week as a number calculated by DATE() DATE: Adds a year +1, to A1, and keeps month and day same for A1, and returns the date in A1 + 1 year.

If you want to get the first day of the next year in January: 1st Jan, then replace month and day by 1.. ,MONTH(A1),DAY(A1) => ,1,1

Hope this helps.

share|improve this answer
    
I'm not sure this does what the OP wanted. This seems to find the weekday of the same date next year, rather than the first occurrence of the same weekday in the next year. Also, it assumes that the week starts with Sunday, which is not the case in many countries. –  Indrek Mar 25 at 9:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.