First day of the next month

Some­times, we want to get the first day of the next month. I will show you two solu­tions how to cal­cu­late this date in Excel. The first solu­tion uses the DATE func­tion. It needs the num­ber of days, months and years. First, we will write the num­ber of years, which we can extract­ed from the cur­rent date using the YEAR func­tion (Fig. 1).

=DATE(YEAR(A2

The same case is with the month num­ber. We are using the MONTH func­tion and extract­ing the num­ber from the date. How­ev­er, we have to remem­ber that we want to have the next month. That’s why we are adding +1. It’s impor­tant that the DATE func­tion will work with num­bers larg­er than 12. It will just go to the next year, or the pre­vi­ous one if the num­ber is low­er than zero. Then, we are putting 1 because we want the first day of the month. And just like that, we have the whole for­mu­la (Fig. 2).

=DATE(YEAR(A2),MONTH(A2)+1,1)

After enter­ing and copy­ing it down, we have the results (Fig. 3).

If we want to go fur­ther into the future, we just have to add more months, let’s say 3 (Fig. 4). 

=DATE(YEAR(A2),MONTH(A2)+3,1)

We can see that the func­tion goes to next years in cer­tain dates (Fig. 5).

If we want to go back in time, we have to sub­tract a num­ber, let’s write ‑3 (Fig. 6). 

=DATE(YEAR(A2),MONTH(A2)-3,1)

And we are in the past. As we can see, the num­ber of years is also cal­cu­lat­ed ana­log­i­cal­ly (Fig. 7). 

The sec­ond solu­tion uses the EOMONTH func­tion. We have our start­ing day and the num­ber of months we are mov­ing. Since we want to have the fist day of the next month, first, we need to go to the last day of the cur­rent month (Fig. 8). 

=EOMONTH(A2,0)

And we have the last day (Fig. 9).

How­ev­er, we want the day after, so we have to add 1 (Fig. 10). 

=EOMONTH(A2,0)+1

Now, we have the first days of the next months (Fig. 11). 

When we want to have the first day of the month that is three months into the future, we just have to add 2 months in the sec­ond argu­ment of the EOMONTH func­tion. It’s because we will go 2 months into the future, and then we have +1 at the end, so it sums up to 3 months (Fig. 12).

=EOMONTH(A2,2)+1

And we have prop­er dates (Fig. 13).

Ana­log­i­cal­ly, when we want to go back in time, we need to write one month more than we real­ly want as a neg­a­tive num­ber because we are going to the last day of the month and then we are adding 1, so in total we are going back 3 months (Fig. 14). 

=EOMONTH(A2,-4)+1

And we have the solu­tion (Fig. 15). 

The results are the same, so it’s up to you which solu­tion you will choose. 

https://www.youtube.com/watch?v=T0ijsD81MIs