# Formula to add one year to date once date has passed



## mayoung (Feb 23, 2017)

I am looking for a formula to insert a date (3/16/2017) into cell B2 and when that date has passed to advance the year only to the next year but keeping the month and day the same (3/16/2018). Plus have it keep advancing to next year as the years pass by.


----------



## Tetra201 (Feb 23, 2017)

Try this:

=DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),3,16)),3,16)


----------



## Logit (Feb 23, 2017)

microsoft excel - How do I add 1 year to a specific date once that date has passed? - Super User

Good resource with VBA and formula examples.


----------



## mayoung (Feb 23, 2017)

Worked Great Tetra201!! Thanks


----------



## Tetra201 (Feb 23, 2017)

You are most welcome.


----------



## ExcelNew7 (Dec 21, 2022)

Thank you so much @Tetra201 this is really helpful!

I am looking for a formula to insert a date (25/4/2022) into a cell and when that date has passed to advance the month only to the next month but keeping the year and day the same (25/5/2022). Plus have it keep advancing to next month as the months pass by.

Is there any chance that you could please write a formula to do this? I think it'd be an adaptation of the one earlier in the thread, but I keep getting syntax errors when I fiddle with it.

Additionally, if you could please explain how it works in words that'd be amazing (I'm just trying to understand how different formulas work etc.) - no worries if this would be too troublesome.

Thanks so much


----------



## Tetra201 (Dec 21, 2022)

See if the following formula works for you:

```
=EDATE("2000-4-25",DATEDIF("2000-4-26",TODAY(),"m")+1)
```


----------



## ExcelNew7 (Dec 21, 2022)

Tetra201 said:


> See if the following formula works for you:
> 
> ```
> =EDATE("2000-4-25",DATEDIF("2000-4-26",TODAY(),"m")+1)
> ```


Thank you! It looks like it's worked (i.e. shows the next due date as 25th Dec 2022). I'll check it again in the next few days and will update on here if it updated for me


----------



## ExcelNew7 (Dec 22, 2022)

@Tetra201 How would I write the formula to do the same kind of thing for quarterly last working day please? Where a quarter ends on the LWD of March, June, September, December.

I.e. I am looking for a formula to insert a date (LWD of December) into a cell and when that date has passed to advance the quarter only to the next quarter (LWD March). Plus have it keep advancing to next quarter as the quarters pass by. (LWD June, LWD Sept).

For example:
- 30/12/2022
- 31/03/2023
- 30/06/2023
- 29/09/2023
- 29/12/2023
- 28/03/2024
etc.

Thank you - you're a pro at these, I've spent a few hours trying to figure this one out and completely stuck still!


----------



## Tetra201 (Dec 22, 2022)

See if the following formula works for you:

```
=WORKDAY(EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3))+1,-1)
```

Note: the formula may return LWD of the quarter for the last non-working days of the quarter. For example, if today is Saturday 31/12/2022, the formula will still return 30/12/2022.


----------



## mayoung (Feb 23, 2017)

I am looking for a formula to insert a date (3/16/2017) into cell B2 and when that date has passed to advance the year only to the next year but keeping the month and day the same (3/16/2018). Plus have it keep advancing to next year as the years pass by.


----------



## ExcelNew7 (Dec 22, 2022)

Thank you very much @Tetra201! 

That formula was exactly what I was looking for, and I think I have been able to adapt it for:
Monthly Last Working Day: WORKDAY(EOMONTH(TODAY(),MOD(1−MONTH(TODAY()),1))+1,−1)
Monthly Last Day: EOMONTH(TODAY(),MOD(1−MONTH(TODAY()),1))

I think these are right, but I would appreciate if you could please check over them - in the second one, I am unsure if not having +1,-1 will cause a problem but adding them seemed to give syntax error.

How would I do one for the 1st working day of the month please? I tried to look at the last working day one, and adapt it, but I'm not very good with coding. 

Thanks again - I wish I could figure out how to write these myself!


----------



## Tetra201 (Dec 22, 2022)

For the Monthly Last Working Day, you need just:

```
=WORKDAY(EOMONTH(TODAY(),0)+1,-1)
```

For the Monthly Last Day, you need just:

```
=EOMONTH(TODAY(),0)
```


----------



## ExcelNew7 (Dec 23, 2022)

Thank you so much - this is all so helpful! 

How would I do Monthly First Working Day please? I tried changing EOMONTH to just MONTH, but this returned an error message 'MONTH requires one argument, but was given 2.'


----------



## Tetra201 (Dec 23, 2022)

See if the following formula works for you:

```
=WORKDAY(EOMONTH(TODAY(),-1),1,Holidays)
```
where _Holidays_ is a named range that contains holiday dates.


----------



## ExcelNew7 (Dec 23, 2022)

Tetra201 said:


> See if the following formula works for you:
> 
> ```
> =WORKDAY(EOMONTH(TODAY(),-1),1,Holidays)
> ...


@Tetra201 Thank you, but when I put that formula in it returns #NAME?, I think this is because I don't know what to do in the holidays bit to name the range. If that's for things like Christmas etc., I'm not too worried - it's more just the Monday-Friday I'm looking for.

=WORKDAY(EOMONTH(TODAY(),−1),1) returns 1st December 2022 - How would I get it to update to show the first working day of next month please?

Thanks very much


----------



## Tetra201 (Dec 23, 2022)

The formula in Post #14 is for the first working day of the _current_ month. For the first working day of the _next_ month, please try:

```
=WORKDAY(EOMONTH(TODAY(),0),1)
```


----------



## ExcelNew7 (Dec 23, 2022)

Tetra201 said:


> The formula in Post #14 is for the first working day of the _current_ month. For the first working day of the _next_ month, please try:
> 
> ```
> =WORKDAY(EOMONTH(TODAY(),0),1)
> ```


Ah that makes sense - thank you so much.

So to confirm, that formula in post 16 does, where FWD = First Working Day: 'I am looking for a formula to insert a date (FWD of January) into a cell and when that date has passed to advance the month only to the next month (FWD of February ). Plus have it keep advancing to next month as the months pass by. (FWD March, FWD April, FWD May etc.)' please?

Thanks again


----------



## mayoung (Dec 23, 2022)

ExcelNew7 said:


> @Tetra201 Thank you, but when I put that formula in it returns #NAME?, I think this is because I don't know what to do in the holidays bit to name the range. If that's for things like Christmas etc., I'm not too worried - it's more just the Monday-Friday I'm looking for.
> 
> =WORKDAY(EOMONTH(TODAY(),−1),1) returns 1st December 2022 - How would I get it to update to show the first working day of next month please?
> 
> Thanks very much


ExcelNew7
For the Holidays, you but your dates in a column in your spreadsheet or on another sheet. You select those cells then go up your header were your cell number is and type in Holidays to name your range. When I have used this I hide my column or sheet. Hope that helps. If not search excel named ranges and I am sure you will find articles or video explaining it. I would search excel using Holiday range in formula.


----------



## ExcelNew7 (Dec 23, 2022)

mayoung said:


> ExcelNew7
> For the Holidays, you but your dates in a column in your spreadsheet or on another sheet. You select those cells then go up your header were your cell number is and type in Holidays to name your range. When I have used this I hide my column or sheet. Hope that helps. If not search excel named ranges and I am sure you will find articles or video explaining it. I would search excel using Holiday range in formula.


Thank you @mayoung - I'll remember that for next time!


----------



## mayoung (Dec 23, 2022)

ExcelNew7 said:


> Thank you @mayoung - I'll remember that for next time!


That is your holiday dates only.


----------



## mayoung (Feb 23, 2017)

I am looking for a formula to insert a date (3/16/2017) into cell B2 and when that date has passed to advance the year only to the next year but keeping the month and day the same (3/16/2018). Plus have it keep advancing to next year as the years pass by.


----------



## ExcelNew7 (Dec 29, 2022)

@Tetra201 Hope you've had a good Christmas (if you celebrate it). I was wondering if you could please help me with this one please: 

I am looking for a formula to insert a date (last day of February 2023) into a cell and when that date has passed to advance the year only to the next year (last day of February 2024). Plus have it keep advancing to the next year as the years pass by. That said, it needs to show the 29th of February on leap years, but 28th of February on non-leap years please.

Thank you


----------



## Tetra201 (Dec 29, 2022)

See if the following formula works for you:

```
=DATE(YEAR(EDATE(TODAY(),10)),3,)
```


----------



## ExcelNew7 (Dec 29, 2022)

Tetra201 said:


> See if the following formula works for you:
> 
> ```
> =DATE(YEAR(EDATE(TODAY(),10)),3,)
> ```


@Tetra201 Thank you for this formula. For some reason though, when I put it into the spreadsheet it does show 28 February 2023, but it tells me 'DATE day argument should be between 1 and 31.' How would the formula be rewritten to avoid that  message showing please? Thank you


----------



## Dave Patton (Dec 29, 2022)

What version of Excel are you using?

The formula works for me.
You can add the 0 at the end.
=DATE(YEAR(EDATE(TODAY(),10)),3,0)


----------



## Tetra201 (Dec 29, 2022)

ExcelNew7 said:


> ... 'DATE day argument should be between 1 and 31.'...


In this case, try:

```
=DATE(YEAR(EDATE(TODAY(),10)),3,1)-1
```


----------



## ExcelNew7 (Dec 29, 2022)

@Dave Patton 16.68. Thank you for that formula, it bought up the same message for me though but unsure why if it worked for you.

@Tetra201 That's amazing - it's worked and cleared the error - thank you very much 

Is the '10' because it is the 10th month if counting back from the end of the year?

What does the '3' do please? Trying to figure out the various components.

Thank you very much


----------



## Dave Patton (Dec 29, 2022)

You did not answer the question. What version of Excel are you using?

3 is for March.

Try reviewing the formula. You can try Excel's Formulas  Evaluate Formula.


----------



## Tetra201 (Dec 29, 2022)

Here is how it works:

- the EDATE(TODAY(),10) part returns the date that is 10 months in the future from today. If today is the last day of February, it returns a date in the end of December of the same year; if today is the first day of March, it returns 1-Jan of the next year;

- the YEAR(EDATE(TODAY(),10)) part returns the year of the date from the previous step;

- the DATE(YEAR(EDATE(TODAY(),10)),3,1) part returns 1-Mar of the year from the previous step;

- finally, the entire formula returns the date that goes just before 1-Mar, that is, the last day of February.

Hope this helps.


----------



## ExcelNew7 (Dec 30, 2022)

@Dave Patton




It says 16.68 is the version but in case you wanted the long number it is in the screenshot attached. Thanks for mentioning the 3 re march - I didn't know why it'd say March but I understand now.

@Tetra201 That's really helpful and very clear! Thank you so much


----------



## ExcelNew7 (Jan 1, 2023)

Tetra201 said:


> The formula in Post #14 is for the first working day of the _current_ month. For the first working day of the _next_ month, please try:
> 
> ```
> =WORKDAY(EOMONTH(TODAY(),0),1)
> ```


@Tetra201 I am looking for a formula to insert a date (FWD of March) into a cell and when that date has passed to advance the quarter only to the next quarter (FWD June). Plus have it keep advancing to next quarter as the quarters pass by. (FWD Sept, FWD Dec).

In order to do this, I think I need to use Monthly First Working Day =WORKDAY(EOMONTH(TODAY(),0),1) and Quarterly Last Working Day =WORKDAY(EOMONTH(TODAY(),MOD(3−MONTH(TODAY()),3))+1,−1) and try to put something together combining parts of each.

I realised when I looked at that Monthly First Working Day formula, that although in December it showed 2nd January, now it is January it is showing 1st February.

Please could I have a formula that shows the First Working Day of the month, and the day after that it then changes to show the first working day of the next month? Rather than just showing the first working day of the next month straight away? I.e. I am looking for a formula to insert a date (FWD of January) into a cell and when that date has passed to advance the month only to the next month (FWD February). Plus have it keep advancing to next month as the months pass by. (FWD March, FWD April).

Thank you very much


----------



## mayoung (Feb 23, 2017)

I am looking for a formula to insert a date (3/16/2017) into cell B2 and when that date has passed to advance the year only to the next year but keeping the month and day the same (3/16/2018). Plus have it keep advancing to next year as the years pass by.


----------



## Dave Patton (Jan 1, 2023)

"
Please could I have a formula that shows the First Working Day of the month, and the day after that it then changes to show the first working day of the next month? Rather than just showing the first working day of the next month straight away? I.e. I am looking for a formula to insert a date (FWD of January) in to a cell and when that date has passed to advance the month only to the next month (FWD February). Plus have it keep advancing to next month as the months pass by. (FWD March, FWD April)."

You can check Excel's help information for the functions Workday and Eomonth; you may also want to check Workday.intl.
You should edit your profile to show that you use Excel for the Mac.

Workday.xlsmAB11-Jan-232First working day this monthMon 02-Jan-233First working day of next monthWed 01-Feb-234The next monthWed 01-Mar-235The next monthMon 03-Apr-2364aCell FormulasRangeFormulaB1B1=TODAY()B2B2=WORKDAY(B1-DAY(B1),1)B3B3=WORKDAY(EOMONTH(B1,0),1)B4:B5B4=WORKDAY(EOMONTH(B3,0),1)


----------



## ExcelNew7 (Jan 1, 2023)

Dave Patton said:


> "
> Please could I have a formula that shows the First Working Day of the month, and the day after that it then changes to show the first working day of the next month? Rather than just showing the first working day of the next month straight away? I.e. I am looking for a formula to insert a date (FWD of January) in to a cell and when that date has passed to advance the month only to the next month (FWD February). Plus have it keep advancing to next month as the months pass by. (FWD March, FWD April)."
> 
> You can check Excel's help information for the functions Workday and Eomonth; you may also want to check Workday.intl.
> ...


Hi @Dave Patton I've edited my profile to say re excel for Mac. 

Thanks for this, but I don't want it in several different cells though - I actually want it all in one cell if that makes sense. The spreadsheet shows several different rows all with different parameters (some annually on different dates, some monthly, some quarterly etc. but I don't want a reference cell. Therefore I've replaced B1 with TODAY 'WORKDAY(TODAY()−DAY(TODAY()),1)'.

I've changed the date time settings of my computer to check if it updates properly, but it doesn't seem to

On the 10th of January it still shows 2nd Jan (Jan FWD) and only updates on the 1st February to show the 1st February (Feb FWD) as the next first working day of the month

What I'm looking for is one that would update on the day after the first working day of the month to then show the first working day of the next month, all in the same cell. For example:
02/12/2022 - 02/01/2023 it'd say 02/01/2023
03/01/2023 - 01/02/2023 it'd say 01/02/2023
02/02/2023 - 01/03/2023 it'd say 01/03/2023
And so on and so forth automatically.

In relation to workday.intl - I'm happy for it to just use Monday-Friday as working days - I'd have to specify substitute bank holidays etc. otherwise and I want it to run automatically rather than needing parts updating yearly please.

It's the same kind of thing as what @Tetra201 was helping me with before '=WORKDAY(EOMONTH(TODAY(),0),1)' - it's just the first working day box needs to change on the day after the first working day rather than just changing on the 1st of the next month please


----------



## Tetra201 (Jan 1, 2023)

ExcelNew7 said:


> ... Please could I have a formula that shows the First Working Day of the month, and the day after that it then changes to show the first working day of the next month? Rather than just showing the first working day of the next month straight away? I.e. I am looking for a formula to insert a date (FWD of January) into a cell and when that date has passed to advance the month only to the next month (FWD February). Plus have it keep advancing to next month as the months pass by. (FWD March, FWD April)...


I am not sure I understood what you need. Having a couple of expected results in addition to the description would be helpful (i.e., on date X, the formula should return Y; while on date X+1, the formula should return Z).

Anyways, the following formula returns FWD 2-Jan-2023 for 31-Dec-2022, 1-Jan-2023, and 2-Jan-2023; however, it starts returning FWD 1-Feb-2023 on 3-Jan-2023:

```
=WORKDAY(EOMONTH(TODAY(),-(WORKDAY(EOMONTH(TODAY(),-1),1)>=TODAY())),1)
```


----------



## ExcelNew7 (Jan 1, 2023)

Tetra201 said:


> I am not sure I understood what you need. Having a couple of expected results in addition to the description would be helpful (i.e., on date X, the formula should return Y; while on date X+1, the formula should return Z).
> 
> Anyways, the following formula returns FWD 2-Jan-2023 for 31-Dec-2022, 1-Jan-2023, and 2-Jan-2023; however, it starts returning FWD 1-Feb-2023 on 3-Jan-2023:
> 
> ...


That's fantastic! Thank you so much  

How would I adapt that formula to do quarterly please? 

I.e. I am looking for a formula to insert a date (FWD of March) into a cell and when that date has passed to advance the quarter only to the next quarter (by 3 months) (FWD June). Plus have it keep advancing to next quarter as the quarters pass by. (FWD Sept, FWD Dec).

02/12/2022 - 01/03/2023 it'd say 01/03/2023
02/03/2023 - 01/06/2023 it'd say 01/06/2023
02/06/2023 - 01/09/2023 it'd say 01/09/2023
02/09/2023 - 01/12/2023 it'd say 01/12/2023

Thank you


----------



## Tetra201 (Jan 1, 2023)

See if the following formula works for you:

```
=WORKDAY(EOMONTH(TODAY(),MOD(2-MONTH(TODAY()),3)-(WORKDAY(EOMONTH(TODAY(),-1),1)>=TODAY())*(MOD(MONTH(TODAY()),3)=0)*3),1)
```


----------



## ExcelNew7 (Jan 1, 2023)

Tetra201 said:


> See if the following formula works for you:
> 
> ```
> =WORKDAY(EOMONTH(TODAY(),MOD(2-MONTH(TODAY()),3)-(WORKDAY(EOMONTH(TODAY(),-1),1)>=TODAY())*(MOD(MONTH(TODAY()),3)=0)*3),1)
> ```


Thank you very much @Tetra201 - that works perfectly


----------

