Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Formula with IF, TODAY and MAX not working.

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    438
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Formula with IF, TODAY and MAX not working.

    I'm not sure why this formula isn't working. If AZ2 = "", then I want to return the value of 0. If it isn't null, I want to get the number of days between Today and the max date in column AZ.

    =IF(AZ2="",0,TODAY()-(MAX(Financials!AZ:AZ)))

    When I enter the formula on the Financials sheet, it works fine (obviously without the sheet reference).

    Thoughts?
    While answers are nice, education and guidance are ideal.

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Tillamook, OR
    Posts
    2,100
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    How is it not working?
    Win 7:MS Excel 2010

    Excel Worksheet Functions (TechOnTheNet)
    Excel Worksheet Functions (ExcelJet) Excel Formulas (ExcelJet)

    Post a copy of your data with one of the following methods:
    Excel Jeanie, MrExcel HTML Maker, Tableizer!

  3. #3
    Board Regular
    Join Date
    Mar 2017
    Posts
    438
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    It's returning a value of 0, but AZ2 is not = "", so it should move to the false portion of the formula.
    While answers are nice, education and guidance are ideal.

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Tillamook, OR
    Posts
    2,100
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    What is in AZ2?
    Win 7:MS Excel 2010

    Excel Worksheet Functions (TechOnTheNet)
    Excel Worksheet Functions (ExcelJet) Excel Formulas (ExcelJet)

    Post a copy of your data with one of the following methods:
    Excel Jeanie, MrExcel HTML Maker, Tableizer!

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,298
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    This AZ2 is a cell of Financials!AZ:AZ?

    And in which sheet are you running your formula?
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular
    Join Date
    Mar 2017
    Posts
    438
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    AZ2 contains a date, and the formula is entered in a sheet called Summaries.
    While answers are nice, education and guidance are ideal.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,298
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    Quote Originally Posted by reberryjr View Post
    AZ2 contains a date, and the formula is entered in a sheet called Summaries.
    Entered in Summaries, say in A4, the formula:

    =IF(AZ2="",0,TODAY()-MAX(Financials!AZ:AZ))

    would give a 0 if AZ2 is empty or a figure that would result from substracting MAX of the AZ column from Today's date.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    Board Regular
    Join Date
    Mar 2017
    Posts
    438
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    I tried that, but it's still returning a value of 0, despite there being a date in AZ2
    While answers are nice, education and guidance are ideal.

  9. #9
    Board Regular
    Join Date
    Jun 2015
    Location
    Tillamook, OR
    Posts
    2,100
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    And that is AZ2 on the 'Summaries' sheet that has a date in it, correct?
    Win 7:MS Excel 2010

    Excel Worksheet Functions (TechOnTheNet)
    Excel Worksheet Functions (ExcelJet) Excel Formulas (ExcelJet)

    Post a copy of your data with one of the following methods:
    Excel Jeanie, MrExcel HTML Maker, Tableizer!

  10. #10
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,814
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula with IF, TODAY and MAX not working.

    Is the workbook on auto-calculate?
    Please make it as easy as possible for people to help you. Have a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel. For a better answer, put more work into asking a better question.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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