Conditional Formatting for Past Due Time

Mike Mills

I have a Task Due Date, Due Time, Completed Date, and Completed Time columns. How can I create a conditional format for tasks that are past the due time. Due date is easy, but I'm stumped on the time...

Paul Newcome

How are your times formatted? Are you using a : ?

Is it 12 hour or 24 hour?

If 12 hour, how exactly are you indicating am vs pm? Is it in the same column as the time or a different column?

Mike Mills

The due time is listed as AM or PM. I need to identify when a task is past the due time using both conditional formatting and Automation email Alert. I would think it needs to compare due time to current time =now() but Smartsheet doesn't recognize this excel formula or have any date/time formulas other than the two modify or create autogenerated fields. Hoping there is a workaround.

TASK NAME DUE DATE DUE TIME COMPLETED DATE COMPLETED TIME

Sample Task 02/25/21 11:00 AM

Thanks.

Paul Newcome

OK. I haven't worked on something quite like this yet, but I do have a few ideas. It may not be until early next week, but I will get back to you once I have completed some testing. I am definitely confident that we can find a solution down to the hour assuming you have access to the Record a Date automation?

Mike Mills

Thank you Paul!

Paul Newcome

@Mike Mills Sorry for the delay. I haven't forgotten about you. I just haven't had a lot of time to focus on this one. I'm working on it...

Paul Newcome

Let's go ahead and get you started on the automated time sheet. We basically use 24 Record A Date automations to update 24 different rows with the current date. One set for each hour of the day. We then use a system generated Modified (date) column so that we can get the time stamp and use a couple of helper columns to pull the most recent date and time (which should be as recent as the turn of the current hour).

Here is a screenshot of how I have mine set up. A more detailed explanation is below the screenshot.

NOTE: I named my sheet "zzzTODAYzzz" primarily for the initial "zzz". It allows me to search for it very easily when doing cell linking.

Today (date column):

In row 1 I entered

=TODAY()

I made this the far left column intentionally. I link to this sheet from many others through cell linking (which allows me to have an automatically updating TODAY function without activating every single sheet every single day). When creating a cell link, I can search for the sheet quickly by entering zzz, and when you first select the sheet to link from the selection defaults to the cell in the top left corner of the sheet. Having this there means I only have to select the sheet and then create the link. I don't have to worry about selecting any specific cell just to get today's date.

[Current Hour] (text/number column):

=SUBSTITUTE(MAX(Modified:Modified), DATEONLY(MAX(Modified:Modified)) + " ", "")

[Last Update] (text/number column):

=MAX(Modified:Modified) + ""

Primary (primary column or any other text/number type of column):

Manually entered a - x on rows 2 - 25

Time (text number column):

Manually entered 0000 - 2300 on rows 2 - 25

[Automation Date] (date column):

Left blank initially. This column will house the dates for the Record A Date automations

Modified (system generated Modified (date) column):

Just let it do its thing.

Automations:

Basically we are going to set up a Record a Date automation called "0000" where the trigger is "Every day starting on (pick any date you want to start running on)".

Set the time for 12:00am.

The condition will be where Primary is equal to a.

Record the date in the [Automation Date] column.

Duplicate it, then edit it. Update the title to 0100, update the time to 1:00am. Update the condition to be where Primary is equal to b.

Keep duplicating and updating until you have an automation set up for each hour.

As you can see from the first screenshot, this is usually pretty good about updating within a few minutes of the hour changing. if you look at the 0900 row, you will see it updated at 9:09am. That's the latest I have seen one run is 9 minutes late.

What we are going to end up doing is using a cell link to pull the time from [Current Hour]1 into the sheet that you want to set up the conditional formatting on. We will pull the hour and use that for comparison to the hour in the [Due Time] column on a row by row basis. The formulas on this conditional formatting sheet are the ones I need to test/tweak. I'll get back to you on those soon.

Paul Newcome

Did you want to flag ANYTHING that is past due including completed rows or only those rows where the completed date/time is blank?

Paul Newcome

Ok. For the Conditional formatting...

We need 3 columns in the working sheet. They are all text/number types.

[Current Hour]1:

Cell link to the current hour cell in the zzzTODAYzzz sheet.

[Current Hour]2:

=YEAR(TODAY()) + IF(MONTH(TODAY()) < 10, "0") + MONTH(TODAY()) + IF(DAY(TODAY()) < 10, "0") + DAY(TODAY()) + IF((VALUE(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1)) + IF(AND(FIND("A", [Current Hour]1) = 0, VALUE(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1)) <> 12), 12, IF(AND(FIND("A", [Current Hour]1) > 0, VALUE(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1)) = 12), -12))) < 10, "0") + (VALUE(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1)) + IF(AND(FIND("A", [Current Hour]1) = 0, VALUE(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1)) <> 12), 12, IF(AND(FIND("A", [Current Hour]1) > 0, VALUE(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1)) = 12), -12)))

This gives us a current date/timestamp in yyyymmddhh format.

Due:

=YEAR([Due Date]@row) + IF(MONTH([Due Date]@row) < 10, "0") + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, "0") + DAY([Due Date]@row) + IF((VALUE(LEFT([Due Time]1, FIND(":", [Due Time]1) - 1)) + IF(AND(FIND("A", [Due Time]1) = 0, VALUE(LEFT([Due Time]1, FIND(":", [Due Time]1) - 1)) <> 12), 12, IF(AND(FIND("A", [Due Time]1) > 0, VALUE(LEFT([Due Time]1, FIND(":", [Due Time]1) - 1)) = 12), -12))) < 10, "0") + (VALUE(LEFT([Due Time]1, FIND(":", [Due Time]1) - 1)) + IF(AND(FIND("A", [Due Time]1) = 0, VALUE(LEFT([Due Time]1, FIND(":", [Due Time]1) - 1)) <> 12), 12, IF(AND(FIND("A", [Due Time]1) > 0, VALUE(LEFT([Due Time]1, FIND(":", [Due Time]1) - 1)) = 12), -12)))

This gives us the Due date/timestamp in yyyymmddhh format.

Late:

=IF(Due@row < $[Current Hour]$2, "Late")

This outputs "Late" for anything that is past due (starting the next hour).

We can modify this to exclude completed rows if needed.

Mike Mills

Fist. I really appreciate the effort, amount of detail in an explanation, and most importantly your time to help identify a "workaround". 2nd, I apologize for not responding soon, I didn't know there was a reply. I'm not getting the notifications. (I'll need to check the settings and my spam folder.)

I will review and run a few tests and let you know. Again thanks for the reply & time.