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

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?

-

## migrated from webapps.stackexchange.comMar 25 '14 at 8:04

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

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 '14 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 '14 at 9:13

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)
``````
-

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.

-

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.

-
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 '14 at 9:11