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

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.

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 Does that make sense?

Welcome to the community, @Johanna_Nikoletos! @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"
)
``````

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:

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.

1 Like

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

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.