Blank Date in Duration

Hello - I am doing a duration calculation where the date could be blank = no report. If the date is blank, I would like the calculation to return a zero or blank in duration cell. Is this possible?

I have gotten this far but everything tried after has given error :)

ROUNDUP([Date Report Delivered to Ops]@row - [Date Report Requested]@row), IF([Date Report Delivered to Ops]@row ISBLANK...

Best Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 01/26/24 Answer ✓

    Hello @Michele R.

    Is this what you're going for?

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report", ROUNDUP([Date Report Delivered to Ops]@row - [Date Report Requested]@row))

    You can replace the words in quotations "No Report" with whatever result you want if a Blank is found.

  • Michele R.
    Michele R. ✭✭
    Answer ✓

    Hey! That works perfectly too! A little different but something I was also thinking of doing. THANK YOU!

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Answer ✓

    I would need to get a visual with screen shots of all relevant columns and sheets to get a clear direction to assist further.

    =NETWORKDAYS will calculate working days, exclude weekends and holidays.

    You'll need a separate column or sheet that lists the dates of your holidays. For example:

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report", NETWORKDAYS([Date Report Requested]@row, [Date Report Delivered to Ops]@row, [Holiday Column]:[Holiday Column]))

    For the second case (difference between multiple dates and finding the latest one):

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report",  ROUNDUP(MAX([Date Checks Mailed]@row, [Date Sent to Accounting]@row, [Date Letters Mailed]@row) - [Date Report Requested]@row))

    Hope this is helpful.

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 01/26/24 Answer ✓

    Hello @Michele R.

    Is this what you're going for?

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report", ROUNDUP([Date Report Delivered to Ops]@row - [Date Report Requested]@row))

    You can replace the words in quotations "No Report" with whatever result you want if a Blank is found.

  • Michele R.
    Michele R. ✭✭
    Answer ✓

    Hey! That works perfectly too! A little different but something I was also thinking of doing. THANK YOU!

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭

    Excellent. Glad it's working for you and is a step in the right direction.

  • Michele R.
    Michele R. ✭✭
    edited 01/26/24

    @Mr. Chris, What if I only wanted to include working days and exclude weekends and holidays. With this formula the way it is written can I add that?

    Additionally, can you calculate using multiple dates? I need to use the latest date between date checks mailed and dates letters mailed for total days.

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report", ROUNDUP([Date Checks Mailed]@row - [Date Sent to Accounting]@row))

    other date is ([Date Letters Mailed]@row

  • @Mr. Chris, What if I only wanted to include working days and exclude weekends and holidays. With this formula the way it is written can I add that?

    Additionally, can you calculate using multiple dates? I need to use the latest date between date checks mailed and dates letters mailed for total days.

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report", ROUNDUP([Date Checks Mailed]@row - [Date Sent to Accounting]@row))

    other date is ([Date Letters Mailed]@row

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Answer ✓

    I would need to get a visual with screen shots of all relevant columns and sheets to get a clear direction to assist further.

    =NETWORKDAYS will calculate working days, exclude weekends and holidays.

    You'll need a separate column or sheet that lists the dates of your holidays. For example:

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report", NETWORKDAYS([Date Report Requested]@row, [Date Report Delivered to Ops]@row, [Holiday Column]:[Holiday Column]))

    For the second case (difference between multiple dates and finding the latest one):

    =IF(ISBLANK([Date Report Delivered to Ops]@row), "No Report",  ROUNDUP(MAX([Date Checks Mailed]@row, [Date Sent to Accounting]@row, [Date Letters Mailed]@row) - [Date Report Requested]@row))

    Hope this is helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!