Days Remaining formula w/ error messages for missing fields

Good Afternoon,

I have a sheet with Start Date , Due Date and Days Remaining Columns. I am to populate the Days Remaining Field using

=[Due Date]@row - TODAY()

I wanted to have a message display in place of the Days Remaining if the Start or Due date are missing. The messages work if I replace the previous formula with

=IF(ISBLANK([Start Date]@row), "Start Date Missing", IF(ISBLANK([Due Date]@row), "Due Date Missing"))

But I can't figure out how to combine the two. I have tried everyway I can think of but all I get a syntax errors.

Thank You

Find more posts tagged with

Accepted answers

James Glenn - NOAA Federal

Thank you very much. It works perfectly. It's also much cleaner that what I previously had.

All comments

Paul Newcome

OD you mean combine as in if both are missing then the cell would say

Start Date Missing - Due Date Missing

Or only one if only one date is missing

Or the difference when both dates are present?

James Glenn - NOAA Federal

Sorry for not being clear,

If Start Date or Both Start and Due Dates are missing it should say "Missing Start Date"

If Just Due Date is missing it should say "Missing Due Date"

Otherwise it should show the calculated number of days remaining.

Paul Newcome

Try this...

=IF([Start Date]@row = "", "Start Date Missing", IF([Due Date]@row = "", "Due Date Missing", [Due Date]@row - TODAY()))

James Glenn - NOAA Federal

Thank you very much. It works perfectly. It's also much cleaner that what I previously had.

Paul Newcome

Happy to help. 👍️

ccortes

This is similar to my question. Do you guys know how the formula to subtract today's date from the Due Date column so it will display the remaining days in the Remaining Days column?

I do not have a today's date column as I am thinking this is auto generated since it is a smartsheet. Any advice?

Paul Newcome

@ccortes You are going to need to use a TODAY function.

=[Due Date]@row - TODAY()

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.