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.
Comments
-
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"))
-
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.
-
I tried this and it did not work do I need to put it after my current formula?
-
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.
-
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.
-
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"), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!