Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Formulas
4501 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Johanna_Nikolet
4 - Data Explorer
4 - Data Explorer

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.

6 Replies 6

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?

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"

Screen Shot 2022-01-29 at 9.25.34 PM

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

Screen Shot 2022-01-29 at 9.28.34 PM

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

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:

Screen Shot 2022-01-31 at 9.05.05 AM

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

Screen Shot 2022-01-31 at 9.05.42 AM

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.

This was perfect - thank you so much!

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

image