Conditional format using simple date calculation

[Kirk_Williams]

Greetings!

I held off on posting here as I suspect there is a simple solution, however, I am embarrassed to say I’ve spent several days on this with no success. My coding experience is extremely limited in general, and pretty much non-existent when it comes to python.

My goal is to apply a conditional format to a date/time column that triggers when a specific number of days have passed since the value in that same column. I’ve attempted to employ the strptime and DATEVALUE functions as described in the documentation, however, it seems to me that both are intended to parse date components from a string field. As the column I’m referencing is already formatted as a date/time column, I’m simply stumped at how to formulate this.

As a workaround, I’ve attempted to create a column that returns the number of days since the reference (to then use the returned numeric value as a condition for the formatting), but I’ve not been able to achieve that either using the formula:

from datetime import datetime

delivery_date = datetime.strptime($Delivery_Date, '%Y-%m-%d %H:%M:%S')
current_date = datetime.now().date()
days_since_delivery = (current_date - delivery_date.date()).days
days_since_delivery

Using the above, the error returned is:

TypeError : strptime() argument 1 must be str, not datetime.datetime

A TypeError is usually caused by trying
to combine two incompatible types of objects,
by calling a function with the wrong type of object,
or by trying to do an operation not allowed on a given type of object.

You tried to concatenate (add) two different types of objects:
a string (str) and datetime.

I’ve honestly lost track of the various attempted methods I’ve tried, including the use of formulas that are working in Excel.

If anyone can offer some guidance, I’d sincerely appreciate it!

The referenced column is $Delivery_Date

I’m simply trying to achieve some type of visual indicator to draw attention to any rows for which 14 or more days have elapsed since the $Delivery_Date value.

I suspect the task would be more simple if the referenced column did not include the time, however, this data is being used to verify certified mail deliveries, so the timestamp is required.

Thanks in advance for any guidance!

[wunter8]

Try a condition like this:

$Delivery_Date.date() <= DATEADD(TODAY(), days=-14)

[natalie-grist]

Hey there!

Never hesitate to reach out with questions - we are always happy to help

You can use the DATEADD() function. For example, if you want to add conditional formatting to delivery dates that are 5 days or more ago, use the following funciton;

TODAY() >= DATEADD($Delivery_Date, days=5)

Thanks,

Natalie

[Kirk_Williams]

@wunter8

$Delivery_Date.date() <= DATEADD(TODAY(), days=-14)

Thank you both, and @wunter8 my apologies for the discord confusion! (I actually had to employ my son’s help with logging in lol!).

I had struck out previously using DATEADD; I presume there was a syntax error in my entry, since it does seem to be working now.

As a quick addendum, I did want to add additional criteria for the conditional format - one based on a “status” column, and another to exclude any rows without a “$Delivery_Date” value. I’ve got the status field variable working; is there a quick tip I can use to also exclude any rows with NULL values?

[wunter8]

$Delivery_Date.date() <= DATEADD(TODAY(), days=-14) if $Delivery_Date and $Status == 'Done' else None

[natalie-grist]

Use and to add additional criteria to the conditional formula.

$Delivery_Date and $Status == "Delivered" and TODAY() >= DATEADD($Delivery_Date, days=5)

$Delivery_Date first checks if there is a value in the Delivery_Date column. If so, this portion is true. If no value exists, then this is false and the entire conditional statement is false.

Next, we check the value in the Status column. If this value is ‘Delivered’ then this portion is true as well.

Finally, we have the prior bit that checks if so many days have passed since the Delivery Date.

If all pieces of the formula are true, the conditional style is applied.

[wunter8]

Natalie’s answer is much more thorough than mine. And her syntax is a bit more straightforward (she doesn’t use the python inline if expression I am using), so I’d recommend using that approach. (Adjust the number of days as necessary)

[Kirk_Williams]

Thank you [both] SO MUCH!!!

Natalie’s formula is working flawlessly. Even after knowing the solution, I NEVER would have been able to derive that syntax on my own. My need for python practice is fully exposed!

I had been using “sequential” formatting rules for the additional conditions. Typically, I can enforce priority of multiple conditions based on their sequence, however, the original DATEADD argument was kicking back the error below (although it was working) that was taking precedence regardless of order:

AttributeError : ‘NoneType’ object has no attribute ‘date’

An AttributeError occurs when the code contains something like
object.x
and x is not a method or attribute (variable) belonging to object.

You are attempting to access the attribute date
for a variable whose value is None.

Using the “and” argument inline returns the exact behavior I was seeking:

Thank you both again for your assistance - I REALLY appreciate your time!