+ Reply to Thread
Results 1 to 9 of 9

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

Hybrid View

  1. 02-19-2025, 03:30 AM #1
    allanortiz
    allanortiz is offline
    Registered User
    Join Date
    05-05-2024
    Location
    riyadh
    MS-Off Ver
    MS365 Version 2403
    Posts
    81

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

    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-19-2025, 03:45 AM #2
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,006

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

    Here, try this:

    =DATE(YEAR(TODAY()),MONTH(E7),DAY(E7))-61
    Never use Merged Cells in Excel
    Reply With QuoteRegister To Reply
  3. 02-19-2025, 04:25 AM #3
    allanortiz
    allanortiz is offline
    Registered User
    Join Date
    05-05-2024
    Location
    riyadh
    MS-Off Ver
    MS365 Version 2403
    Posts
    81

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

    Thank you Zbor its working good.
    Reply With QuoteRegister To Reply
  4. 02-19-2025, 05:00 AM #4
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,006

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

    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
    Reply With QuoteRegister To Reply
  5. 02-20-2025, 05:02 AM #5
    allanortiz
    allanortiz is offline
    Registered User
    Join Date
    05-05-2024
    Location
    riyadh
    MS-Off Ver
    MS365 Version 2403
    Posts
    81

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

    Thank you Zbor for the correction it working well now .
    Reply With QuoteRegister To Reply
  6. 02-19-2025, 05:48 AM #6
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,982

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

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

    custom format dd-mm-yyyy


    Copy down
    Last edited by CARACALLA; 02-19-2025 at 07:18 AM.
    Reply With QuoteRegister To Reply
  7. 02-20-2025, 05:03 AM #7
    allanortiz
    allanortiz is offline
    Registered User
    Join Date
    05-05-2024
    Location
    riyadh
    MS-Off Ver
    MS365 Version 2403
    Posts
    81

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

    Thanks Caracalla, its working too.
    Reply With QuoteRegister To Reply
  8. 02-19-2025, 06:30 AM #8
    Phuocam
    Phuocam is online now
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,172

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

    Another way:

    =EDATE(E7,CEILING(DATEDIF(E7,TODAY(),"m")+1,12)-2)
    Reply With QuoteRegister To Reply
  9. 02-20-2025, 05:03 AM #9
    allanortiz
    allanortiz is offline
    Registered User
    Join Date
    05-05-2024
    Location
    riyadh
    MS-Off Ver
    MS365 Version 2403
    Posts
    81

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

    Thanks Phuocam, this works well too. appreciated your help guys
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. vba if any date in not belongs to current year then convert the date in current year
    By nkashyap3 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2019, 07:13 AM
  2. formula to take most current date from dates in 3 columns and add 6 months to the recent
    By DocHollywood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2019, 10:42 PM
  3. [SOLVED] Dropdown list with formula for months of the current year till date
    By mohanys in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-18-2019, 10:37 PM
  4. [SOLVED] VBA Insert Current Month, Year and subsequent months/year for 15 months
    By MichiganWilliams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2014, 03:49 PM
  5. [SOLVED] Keeping a date as current year, even though formula determines previous year
    By dropanddrive03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2013, 08:31 PM
  6. Formula to calculate the percentage of a year using a start date and year to date dates
    By pammy66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2012, 05:14 AM
  7. DATES: Calculate the Number of months in each year for a Date Span
    By chconnol in forum Excel General
    Replies: 5
    Last Post: 06-07-2008, 02:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1