Search

# When is the next Monday? [Homework]

Psst. Hey you, yeah, you there… have time for a quick date? A date with Excel that is.

Assuming you have an input date in cell A1,

• What is the formula for finding next Monday?
• What is the formula for finding first Monday of next month?

Note: If input date is a Monday, you need to calculate the next one.

Need a clue? Check out Excel Date & Time concepts.

Try to solve the problem using either Power Pivot or Power Query. In both cases, assume your input is called [input date] or #”input date”

Craving for more?  Other Excel homework problems for you.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: date and time, homework Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 31 Responses to “When is the next Monday? [Homework]”

1. next Monday:
=A1+7-MOD(A1-2,7)
first Monday of next month:
=DATE(YEAR(A1),MONTH(A1)+1,0)+7-MOD(DATE(YEAR(A1),MONTH(A1)+1,-2),7)

ciao
r

• Pavel says:

I don’t like using function MOD in date calculation, I prefer WEEKDAY but philosophy is same.
Nest Monday:
=A1+7- WEEKDAY(A1-2,3)
first Monday of next month:
=DATE(YEAR(A1),MONTH(A1)+1,0)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),12)

2. Jozef Melichar says:

=A1+(8-WEEKDAY(A1;2))

3. TheQ47 says:

For next Monday:
=IF(A1+2-WEEKDAY(A1)+(1-(2>=WEEKDAY(A1)))*7=A1,A1+7,A1+2-WEEKDAY(A1)+(1-(2>=WEEKDAY(A1)))*7)

and for First Monday of Next Month:
=DATE(YEAR(A1),MONTH(A1)+1,3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))+(1-(2>=WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))))*7

Those formulas above mine are easier and shorter, though. Goes to show how when you get stuck into working on a formula, you can sometimes work too hard and get too complicated.

4. Chihiro says:

I do this in several ways when using PowerQuery/PowerPivot

1 Using SQL/Native query
TSQL - Replace getdate() with relevant column
Next Monday
Select dateadd(week, datediff(week, 0, getdate()), 7)

First Monday Next Month

2 Using M
Next Monday

First Monday Next Month

5. Chihiro says:

Oh, and in DAX, I never use it. As I almost always have date dimension table in the model. But it would be pretty much same as Excel version.

Ex: Next Monday
=[Input date]+(8-WEEKDAY([Input date],2))

6. Brian says:

Next Monday:

=CHOOSE(WEEKDAY(A1),A1+1,A1+7,A1+6,A1+5,A1+4,A1+3,A1+2)

Next Month 1st Monday:

=CHOOSE(WEEKDAY(DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1))),DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1))+1,DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1)),DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1))+6,DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1))+5,DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1))+4,DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1))+3,DATEVALUE(MONTH(A1)+1&"/1/"&YEAR(A1))+2)

7. Chander Joshi says:

If reference date is in Cell A1

Next Week's Monday =A1+9-WEEKDAY(A1)

Next Month's Monday =EOMONTH(A1,1)+9-WEEKDAY(EOMONTH(A1,1))

8. Haz says:

Next Monday:
=A1+7-WEEKDAY(A1,3)

First Monday of next month:
=EOMONTH(A1,0)+7-WEEKDAY(EOMONTH(A1,0),3)

9. David N says:

Next Monday: =WORKDAY.INTL(A1,1,"0111111")

First Monday of next month: =WORKDAY.INTL(EOMONTH(A1,0),1,"0111111")

The 0111111 string tells WORKDAY.INTL to treat every day except Monday as a weekend -- i.e. not a workday -- so that it can find the date that is 1 workday after the date in A1. And EOMONTH does it's normal job of returning the last day of the A1 month to use as the starting point in searching for the next Monday (first Monday of the following month).

10. Roderick O says:

Next Monday
=A1+7-WEEKDAY(A1,12)

11. Mandeep says:

for next monday
=A1+ CHOOSE(WEEKDAY(A1, 2), 7,6,5,4,3,2,1)

for 1st Monday next month
=DATE(YEAR(A1),MONTH(A1)+1,1+1*7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,8-2))

12. Chris says:

=IF(WEEKDAY(A2)=1,A2+1,A2+9-WEEKDAY(A2))

13. Amit Jalan says:

For Next Monday:
=IF(WEEKDAY(A1)=1,A1+1,A1+9-WEEKDAY(A1))

For First Monday of next Month
=IF(WEEKDAY(EOMONTH(A1,0))=1,EOMONTH(A1,0)+1,EOMONTH(A1,0)+9-WEEKDAY(EOMONTH(A1,0)))

14. ankur murarka says:

current cell containing date + 9 - weekdays( currrent cell containing date)

15. Brian says:

I supply a date from the week in cell A2, such as 9/2/18. It could in fact be any date from this week.

In cell B2, I enter: =a2-weekday(a2)+2. This will calculate the date for Monday of the week in question.

If you need additional Mondays, use the result in cell B2 and add 7.

I use this method to generate a two-week record keeping calendar. Works like a charm.

16. Rob says:

I seem to have gone a little more complicated than some but this is my effort.

Next Monday
=IF(WEEKDAY(A1,1)=1,A1+1,A1+(9-WEEKDAY(A1,1)))

For first Monday
=IF(WEEKDAY(EOMONTH(A1,0),1)=1,EOMONTH(A1,0)+1,A1+(9-WEEKDAY(EOMONTH(A1,0),1)))

17. Daniel N says:

First Monday of next month:

{=DATE(YEAR(A1),MONTH(A1)+1,SUM({1;2;3;4;5;6;7}*(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,{1;2;3;4;5;6;7}),2)=1)))}

Horrible... no doubt, but it works 😉

18. miaousse says:

for first monday
=A1+CHOOSE(Weekday(A1);1;7;6;5;4;3;2)
for frst monday the month after
=EOMONTH(A1;0)+1+CHOOSE(WEEKDAY(EOMONTH(A1;0)+1);1;0;6;5;4;3;2)

19. sezer says:

=IF(WEEKDAY(EOMONTH(A1;0)+1;2)=1;EOMONTH(A1;0)+1;EOMONTH(A1;0)+1+8-WEEKDAY(EOMONTH(A1;0)+1;2))

20. Robert H. Gascon says:

Next Monday: =A1+7-WEEKDAY(A1+7-2)
The 7 means Next while the 2 means Monday. So, this formula means Next Week - Weekday of Adjusted Next Week.

First Monday of Next Month: =EOMONTH(A1,0)+1+7-WEEKDAY(EOMONTH(A1,0)+1-2)
The foregoing formula uses the same logic as the first. So, this formula means One Week after the First Day of Next Month - Weekday of the Adjusted First Day of Next Month.

• Peter Bartholomew says:

Robert
One of the shorted formulas but why the +7 in the WEEKDAY parameter?
I tried your formula with my use of Names

= Input_Date + 7 - WEEKDAY(Input_Date - wd)
and
= first + 7 - WEEKDAY(first - wd)

21. Peter B says:

= CEILING( Input_Date - wd, 7 ) + wd
(where wd=2 gives Mondays)

Similarly
= CEILING( first-wd, 7 ) + wd
where 'first' is a named formula
= EOMONTH( +Input_Date, 0) + 1

Note: The + is needed in front of the date parameter to ensure the formula still works if Input_Data became an array.

=DATE(YEAR(J11),MONTH(J11),WEEKDAY(DATE(YEAR(J11),MONTH(J11),DAY(J11)),1)+6)

=EOMONTH(J11,0)+1

23. Meagan says:

Late to the party, but...

Next Monday:
=InputDate+CHOOSE(WEEKDAY(InputDate,2),7,6,5,4,3,2,1)

First Monday Next Month:
=EOMONTH(InputDate,0)+CHOOSE(WEEKDAY(EOMONTH(InputDate,0),2),7,6,5,4,3,2,1)

24. Eduardo says:

I created a list with the months and their number... (January-1, February-2, and put at the number the name of the month, the next was...

F3= "The month"
=VLOOKUP(IF(INDIREC(F3,0)+1>12,INDIRECTO(F3,0)-11,INDIRECTO(F3,0)+1),\$J\$2:\$K\$13,2,0)

• Eduardo says:

sorry I had a little mistake...

For the next Monday in the next month:
F8=The first date

=SI(MONTH(DATE(YEAR(F8),MONTH(F8),DAY(F8)+28))=MONTH(F8),DATE(YEAR(F8),MONTH(F8),DAY(F8)+35),DATE(YEAR(F8),MONTH(F8),DAY(F8)+28))

25. SG Kenny says:

Next Monday: =B3-WEEKDAY(B3,2)+8

First Monday of next month: =EOMONTH(B3,0)+1+MOD(SUM(7-WEEKDAY(EOMONTH(B3,0)+1,3)),7)

• SG Kenny says:

Sorry, B3 should say A1 (it was just the way my sheet was set up to work this out!)

 « Performing Maths in Microsoft Word (In an Excel Blog) 5 tips: Power Query for Accountants (and finance people) – Free Masterclass »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.