Using formula to flag if date is older than 2 weeks, 4 weeks, 6 weeks

[Johanna_Nikolet]

I’m trying to add a column to a view that calculates - if X date (date a specific record was updated) is older than 2 weeks, show text “2 week” in the corresponding cell. And so on… for all the weeks - 2, 4, 6, and 8. Is this something that can be done?

I’ve tried playing around with IF statements, but I’m not sure how to have it detect today’s date minus 14 for 2 weeks, today’s date minus 28 for 4 weeks, etc.

[Rupert_Hoffsch1]

Hi there! There seem to be two things at play here: Date a record was last modified (that’s a field type) and a formula field using the formula DATETIME_DIFF to calculate the difference between today and that last modified field in weeks. This will give you a number.

Then you can use another formula field to use an if() or switch() statement to go through those cases. Alternatively you could use a formula field to just print {field_with_number}&“week(s)”, although that would also give you uneven numbers.

So there are a couple of options here :slightly_smiling_face: Does that make sense?

[Justin_Barrett]

Welcome to the community, @Johanna_Nikoletos! :grinning_face_with_big_eyes: @Rupert_Hoffschmidt has some good suggestions. One trick that would help is to use the FLOOR() function with a significance of 2, which will effectively round the passed value to some multiple of 2. Sadly there’s a bug with this (and with its cohort, CEILING()), but the workaround is to use this structure (using FLOOR() as the example; the same concept works with both functions):

FLOOR(number / significance) * significance

Credit goes to an Airtable support staffer for sharing this some time ago.

Here’s how you could apply that in this use case:

(FLOOR(DATETIME_DIFF(NOW(), {Last Updated}, "weeks") / 2) * 2) & " weeks"

If you don’t want to see “0 weeks” and just start the output once it hits 2 weeks, use this version:

IF(
    FLOOR(DATETIME_DIFF(NOW(), {Last Updated}, "weeks") / 2) * 2, 
    (FLOOR(DATETIME_DIFF(NOW(), {Last Updated}, "weeks") / 2) * 2) & " weeks"
)

[Alexey_Gusev]

i’m just interested - will it work for
IF(x>=2, EVEN(x-2)&’ weeks’ )
for x=DATETIME_DIFF(NOW(), {Last Updated}, “weeks”) ?

can’t create test dataset now, but i’ll check tomorrow, if you removed yours

[Justin_Barrett]

Alexey_Gusev:

IF(x>=2, EVEN(x-2)&’ weeks’ )

I completely forgot about the EVEN() function. While on the surface it feels like it might do the same thing that the FLOOR() function does, there’s a slight difference. If I replace the FLOOR() function with your EVEN() configuration as written, the values are off:

If I take out the “-2” part, it’s closer, but still not an exact match:

As you can see, EVEN() starts rounding up to 4 before the date is a full 4 weeks back, whereas FLOOR() doesn’t do that.

[Johanna_Nikolet]

This was perfect - thank you so much!

[Alexey_Gusev]

Yeah, i just used some math functions to perform ‘convert decimal to binary and vice versa’ (to complete “items that absent from full set” task), that’s why i remember EVEN function at first.
My fault, i calculated edge cases wrong, should be -1 instead of -2