none
Use of conditional logic in Excel formula, e.g., if NULL, then... RRS feed

  • Question

  • I'm a SQL guy and know next to nothing about Excel formulas. I'm optimistic someone who knows this off the top of their head will help me, please.
    -----------
    Given two date columns, a Start Date and an End Date, where Start Date always contains a date but where End Date could either contain a date or be blank/Null, could you please show me a simple formula for a third Integer column that would show:

    1. the number of elapsed days from Start Date to End Date if End Date is populated
    OR
    2. elapsed days from Start Date to Today if End Date is blank/Null?

    I can see that Start Date - End Date = Days between Dates and that's good. I don't know how to use an IF or IIF to deal with a NULL End Date. Any help appreciated.


    HomeCookN

    Saturday, July 22, 2017 5:43 PM

Answers

  • Like this:

    =IF(EndDate="",TODAY(),EndDate)-StartDate

    For example, if the Start Date is in cell B2 and the end date in cell C2, you could enter the following formula in D2:

    =IF(C2="",TODAY(),C2)-B2

    This can be filled down to the rows below.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by HomeCookN Saturday, July 22, 2017 7:03 PM
    Saturday, July 22, 2017 6:56 PM

All replies

  • Like this:

    =IF(EndDate="",TODAY(),EndDate)-StartDate

    For example, if the Start Date is in cell B2 and the end date in cell C2, you could enter the following formula in D2:

    =IF(C2="",TODAY(),C2)-B2

    This can be filled down to the rows below.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by HomeCookN Saturday, July 22, 2017 7:03 PM
    Saturday, July 22, 2017 6:56 PM
  • Beautiful, Hans! This is exactly what I wanted. I really appreciate your time.

    HomeCookN

    Saturday, July 22, 2017 7:04 PM