Blank Date in Duration

Michele Rosenberg

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...

=Chris Palmer

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 Rosenberg

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

=Chris Palmer

https://community.smartsheet.com/discussion/comment/416581#Comment_416581

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.

=Chris Palmer

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 Rosenberg

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

=Chris Palmer

https://community.smartsheet.com/discussion/comment/415972#Comment_415972

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

Michele Rosenberg

@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

Michele Rosenberg

@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

=Chris Palmer

https://community.smartsheet.com/discussion/comment/416581#Comment_416581

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.