+ Reply to Thread
Results 1 to 5 of 5

DATE minus TODAY formula

  1. #1
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    DATE minus TODAY formula

    Hi all,

    In short, I work with food products and they all have an expiry date such as 19-05-2019 (19th May 2019). The retail chains which I sell my products to demand to have 75% of the products shelf life. Therefore I need a formula that can calculate the date (or number of days) which corresponds to 75% of the products shelf life as of TODAY.

    What does the correct formula look like if A1 = 19-05-2019 and B1 = TODAY()


    I hope you can help me.
    Last edited by Kagesen; 06-27-2018 at 05:20 PM.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,682

    Re: DATE minus TODAY formula

    may be
    =A1-B1
    OR
    =A1-TODAY()

    When hit the enter key if you notice the result in date format change the cell format to number or general
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: DATE minus TODAY formula

    Formula = A1-TODAY() will give me the number of days remaining of the shelf life of the product. In another cell I can then multiply that result with 0,75 to reach the number of days the retail store has to sell the product. In a third cell I then add that number to TODAY() to reach my goal of the date which I'm after.

    I had hoped for a one line formula, but due to the formatting with dates and numbers that will be difficult. This is a workable solution though.

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,346

    Re: DATE minus TODAY formula

    I think this formula gives you the date by which the product should be in the supermarket in one formula

    =A1-ROUNDUP((A1-TODAY())*0,75;0)

  5. #5
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: DATE minus TODAY formula

    Hi Roel Jongman,

    You suggestion rocks! It worked perfectly. Thanks

+ 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. [SOLVED] Today's date minus certain date
    By kao in forum Excel General
    Replies: 5
    Last Post: 11-19-2017, 12:15 AM
  2. [SOLVED] Adding 4 days into the date today minus the weekend
    By geliedee in forum Excel General
    Replies: 5
    Last Post: 05-22-2015, 09:44 AM
  3. Autofill Date with =Today() function minus weekends.
    By slkenda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-23-2013, 02:09 AM
  4. [SOLVED] Formula for today's date minus another date
    By Ocean Zhang in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2013, 08:44 PM
  5. [SOLVED] If Statement with today's date minus due date for a delay calculation
    By RDFUC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2013, 02:58 PM
  6. [SOLVED] Macros for: If Today's Date minus other date is greater than certain amount of days Then
    By lottidotti in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-24-2012, 04:46 PM
  7. Today's date minus two
    By mldardy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-12-2011, 11:51 AM

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