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.
31 Responses
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
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)
=A1+(8-WEEKDAY(A1;2))
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.
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)
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))
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)
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))
Next Monday:
=A1+7-WEEKDAY(A1,3)
First Monday of next month:
=EOMONTH(A1,0)+7-WEEKDAY(EOMONTH(A1,0),3)
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).
Genius ….
Nice use of WORKDAY.INTL for solving this problem!
I believe many users are not aware of the existence and usage of it, and its brother – NETWORKDAY.INTL which I have a blogpost about it:
https://wmfexcel.com/2016/02/06/calculate-number-of-day-in-a-given-period/
Remind that these functions are for Excel 2010 or later versions though.
Next Monday
=A1+7-WEEKDAY(A1,12)
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))
=IF(WEEKDAY(A2)=1,A2+1,A2+9-WEEKDAY(A2))
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)))
current cell containing date + 9 – weekdays( currrent cell containing date)
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.
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)))
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 😉
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)
=IF(WEEKDAY(EOMONTH(A1;0)+1;2)=1;EOMONTH(A1;0)+1;EOMONTH(A1;0)+1+8-WEEKDAY(EOMONTH(A1;0)+1;2))
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.
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)
= 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
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)
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)
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))
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)
Sorry, B3 should say A1 (it was just the way my sheet was set up to work this out!)