Formula Help

PrestonLSidney

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.

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.

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"), "")