How to calculate the first occurrence of a given weekday in the next year in Excel?

Question

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?

Answer 1

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)

Answer 2

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)

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.

Answer 3

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.