+ Reply to Thread
Results 1 to 5 of 5

Counting an expiration date

  1. #1
    Registered User
    Join Date
    05-15-2017
    Location
    Frederick MD
    MS-Off Ver
    365 ProPlus
    Posts
    39

    Question Counting an expiration date

    Hello all, i did i quick search to see if this issue has already been resolved and couldn't find anything.
    My issue is as follows:
    I need a calculation that will do the following:
    Compare today's month/year with the expiration date provided. If it is less than 2 years away, Use that date. If it is greater than 2 years, then use today's month/year +2

    I can't figure out what function to even use
    IF(YEAR($D$11)>=(YEAR(TODAY())+2),TODAY()+YEAR(2),$D$11)

    Thats what i have so far, needless to say it doesn't work. Can you steer me in the direction of properly using date data?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Counting an expiration date

    Something like below?
    =IF($D$11>=DATE(YEAR(TODAY())+2,MONTH(TODAY()),DAY(TODAY())),DATE(YEAR(TODAY())+2,MONTH(TODAY()),DAY(TODAY())),$D$11)

    But I'd recommend storing Today() as fixed value (in another column/cell). Since formula will update the date each time calculation is performed.
    Short cut for today's date is CTRL + ; (Semi-colon)

  3. #3
    Registered User
    Join Date
    05-15-2017
    Location
    Frederick MD
    MS-Off Ver
    365 ProPlus
    Posts
    39

    Re: Counting an expiration date

    I think you solved it for me! Now I am going to try and use this in a nested IF statement and hope it all works! Thank you so much for giving me the kernel to build off of. THANK YOU!

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Counting an expiration date

    You are welcome. Thanks for the rep

  5. #5
    Registered User
    Join Date
    02-17-2012
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Counting an expiration date

    Have you tried something like:

    =IF(TODAY()<=DATE(YEAR(B3)+2,MONTH(B3),DAY(B3)),B3,DATE(YEAR(TODAY())+2,MONTH(TODAY()),1))

    where B3 is the expiry date?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate 1-year expiration date to the end of month of a date
    By Dietcoke1953 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-18-2016, 01:20 PM
  2. Expiration Date safe from changing computer's date
    By backyardfun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2014, 05:58 PM
  3. [SOLVED] Expiration Date on Macro
    By backyardfun in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-28-2013, 11:38 AM
  4. Expiration Date
    By stevenchaplin in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-23-2013, 03:56 PM
  5. [SOLVED] calculation of expiration date
    By deth in forum Excel General
    Replies: 8
    Last Post: 04-15-2012, 05:20 PM
  6. set expiration date
    By nhat8121 in forum Excel General
    Replies: 13
    Last Post: 01-05-2007, 10:02 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1