Formula Help

I am trying to create formula that subtracts to different dates in my data but instead of displaying the date of the result I want it to display "On Time" if less than or equal to zero and "Late" if greater than 0.

I got the formula to display correctly but it is auto displaying in my columns where no data is entered.

Find more posts tagged with

Formulas

Comments

Sterling

Hi Preston,

If I understand correctly the issue is that rows that are blank still show a value for "On Time"? If this is the case you could use a formula to check that one of the date cells isn't blank before moving on to the next step of the formula. An example could look something like this:

=IF(ISBLANK([Date Collumn 1]1), "", IF(([[Date Collumn 2]1 - [Date Collumn 1]1) <= 0, "On Time", "Late"))

Paul Newcome

Another way to write it would be

=IF(ISDATE([Date Column 1]@row), IF([Date Column 2]@row - [Date Column 1]@row > 0, "Late", "On Time"), "")

PrestonLSidney

Will this prevent the "On Time" and "Late" from appearing in cells without dates in them. I only want it to appear if I input a date.

PrestonLSidney

I tried this and it did not work do I need to put it after my current formula?

Sterling

It does, it also prevents it from showing if a non-date value is input into the first date column. My suggestion only works if the first cell is blank, otherwise it causes an error so I'd go with Paul's suggestion.

Sterling

which aspect of it isn't working? I have a test sheet with the formula on it and it seems to work as expected for me. I've attached screenshots of the outputs as well as the formula.

Screen Shot 2018-11-06 at 12.14.33 PM.png

Screen Shot 2018-11-06 at 12.16.47 PM.png

Paul Newcome

A few questions...

What are your actual column names?

Use them in place of [Date Column 1] and [Date column 2] (but leave the @row).

Do you want it to show a value only if BOTH dates are input but blank if both date cells are not input (one or both dates blank)?

=IF(AND(ISDATE([Date Column 1]@row), ISDATE([Date Column2]@row)), IF([Date Column 2]@row - [Date Column 1]@row > 0, "Late", "On Time"), "")

Do you want it to show a value if at least ONE date is input (value will appear if one is blank and the other is not or if both dates are input)?

=IF(OR(ISDATE([Date Column 1]@row), ISDATE([Date Column2]@row)), IF([Date Column 2]@row - [Date Column 1]@row > 0, "Late", "On Time"), "")

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?