Share via

Today() Returning 0

Anonymous
2022-08-18T01:59:54+00:00

In this formula, =IF((C14=" "), (TODAY() -B14), (C14-B14)), I am getting an unexpected result. The false condition calculates as expected but the true condition just returns -B14. Columns B and C are formatted mm/dd/yyyy. When I just enter TODAY() -B14 in another cell I get the correct result. Any idea as to why this is occurring would be appreciated.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2022-08-18T06:57:17+00:00

    @FC, please do not thank or otherwise respond to irrelevant responses that offer no help for you whatsoever.

    You assume that the IF function is evaluating TODAY()-B14 (value-if-true) and, therefore, TODAY() returns zero. Why?!

    IMHO, the more likely conclusion is that the IF function is evaluating C14-B14 (value-if-false), and C14 is zero.

    For example, C14 might be empty (no value).

    I think you try to test for that with C14=" " (one space).

    But the correct test is C14="" (null string).

    You can use the Evaluate Formula feature to step through the formula evaluation and confirm that C14-B14 is evaluated, not TODAY()-B14.

    Also, be sure that your formulas return "" (null string), not " " (one space), when you want a cell to appear to be blank ("empty").

    Then, the test C14="" is equivalent to testing if C14 is empty __or__ it contains the null string.

    Aside.... There is nothing wrong with your date calculations as-is. IMHO, there is no reason for you to consider using DATEDIF instead.

    2 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-08-18T14:19:43+00:00

    joeu2004, thanks so much for pointing out the error I made. Eliminating the space between the quotation marks solved the problem.

    0 comments
  2. Anonymous
    2022-08-18T11:11:26+00:00

    Hi FC

    You may also try the formula

    =IF(C14="",TODAY(),C14)-B14

    Regards

    Jeovany

    0 comments
  3. Anonymous
    2022-08-18T04:32:07+00:00

    Hello,

    Greetings for the day!

    Incase if you have to calculate date difference, could you please try with "DATEDIF" formula. You can calculate differences based on different units.

    https://support.microsoft.com/office/8235e7c9-b430-44ca-9425-46100a162f38

    Syntax: DATEDIF(start_date,end_date,unit)

    https://support.microsoft.com/office/25dba1a4-2812-480b-84dd-8b32a451b35c?ui=en-us&rs=en-us&ad=us

    Please note if the Start_date is greater than the End_date, the result will be #NUM!.

    Also I tried the formula that you have provided and its working fine - =IF((B17=" "), (TODAY() -C17), (D17-C17)) Please make sure you have done date formatting for those columns

    Sample data Column (B17) Column (C17) Column(D17) Test 1/1/2001 1/1/2003

    Result: 730

    Hope this information helps. Please feel free to get back if you have any questions.

    Thank you! Ravikumar Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

    0 comments