Formula for Dates to know which date on current year before 2 months of recontract date

[allanortiz]

Dear Sir's ,

is there any formula to know which date on the currect year , when i need to inform the manager of employee 2 months before his recontracting date.

Thank you

[zbor]

Here, try this:

=DATE(YEAR(TODAY()),MONTH(E7),DAY(E7))-61

[allanortiz]

Thank you Zbor its working good.

[zbor]

Sorry, it was not OK...

If you look 2012-06-16 it will correctly return 2025-04-17
But 2015-02-01 will return 2024-03-12 that has already passed.. And it should return 2025-03-12 that will inform you for a next year expiriy.

Modified:

=IF((DATE(YEAR(TODAY()),MONTH(E7),DAY(E7))-61)<TODAY(),365+DATE(YEAR(TODAY()),MONTH(E7),DAY(E7))-61,DATE(YEAR(TODAY()),MONTH(E7),DAY(E7))-61)

Hiring Date 	Old	         New
2012-06-16	2025-04-17	2025-04-16
2016-06-23	2025-04-24	2025-04-23
2019-07-25	2025-05-26	2025-05-25
2012-06-16	2025-04-17	2025-04-16
2015-02-01	2024-12-03	2025-12-02
2013-04-01	2025-01-31	2026-01-30
2020-05-05	2025-03-06	2025-03-05
2020-09-23	2025-07-25	2025-07-24
2021-03-01	2024-12-31	2025-12-30
2016-06-23	2025-04-24	2025-04-23
2016-02-21	2024-12-23	2025-12-22

[allanortiz]

Thank you Zbor for the correction it working well now .

[CARACALLA]

F7=EDATE(SUBSTITUTE(E7,LEFT(E7,4),YEAR(TODAY()))+0,-2)

custom format dd-mm-yyyy


Copy down

[allanortiz]

Thanks Caracalla, its working too.

[Phuocam]

Another way:

=EDATE(E7,CEILING(DATEDIF(E7,TODAY(),"m")+1,12)-2)

[allanortiz]

Thanks Phuocam, this works well too. appreciated your help guys