When is the next Monday? [Homework]

Excel Challenges - 27 comments

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

If so, take up this homework problem and post your answers in comments.

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.

Post your answers in comments

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

Want additional challenge?

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”

Go ahead and post your answers. Your date is waiting…

Craving for more?  Other Excel homework problems for you.

 

Chandoo

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.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

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

27 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
    SELECT dateadd(week,datediff(week,0,dateadd(day, 6 - datepart(day, dateadd(month, 1, getdate())), dateadd(month, 1, getdate()))), 0)

    2 Using M
    Next Monday
    =Date.AddWeeks(Date.StartOfWeek([Input date],1), 1)

    First Monday Next Month
    =Date.AddWeeks(Date.StartOfWeek(Date.AddMonths(#date(Date.Year([Input date]),Date.Month([Input date]),1),1)-#duration(1,0,0,0),1),1)

  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.

  22. Quadri Adebayo says:

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

Leave a Reply


« »