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.
+21Hi 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?
+21Welcome 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"
)
+25Welcome 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"
)
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
+21i’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.
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"
)
This was perfect - thank you so much!
+25I 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.
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
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.