Apr 01, 2022 08:15 AM
Hi Everyone
Does any one know of a formula that could deduce the Week Number in the Year from a given date field?
Any help would be greatly appreciated.
Stephen
Solved! Go to Solution.
Apr 01, 2022 08:37 AM
Airtable has a WeekNum function that will allow for you to create week numbers. You create a column and make the field type formula and put the formula below.
Ex. WEEKNUM(Enquiry Date/Time)
Apr 01, 2022 08:37 AM
Airtable has a WeekNum function that will allow for you to create week numbers. You create a column and make the field type formula and put the formula below.
Ex. WEEKNUM(Enquiry Date/Time)
Apr 01, 2022 09:17 AM
Lina, you’re an Angel. I’ll give this a shot when I’m back at a computer. Much appreciated.
Apr 01, 2022 11:27 AM
Worked brilliantly. Exactly what I needed. Thanks again.
Feb 02, 2024 11:23 AM
Hi!
Nice shot!
But I need to be a bit further, and I am facing errors.
This is my formula:
"
"
It is running well, but produces an error when the "End Date" is in the past year (2023).
We are in 2024 and it considers Dec/26/2023 as a date in the "FUTURE", due to the pure week number of the "End Date"
I tried with this other formula, but it do not runs:
"
IF(
{End Date},
IF(
YEAR({End Date}) < YEAR(TODAY()),
"PAST",
IF(
YEAR({End Date}) = YEAR(TODAY())
AND VALUE(DATETIME_FORMAT({End Date}, 'W')) < VALUE(DATETIME_FORMAT(TODAY(), 'W')),
"PAST",
IF(
YEAR({End Date}) = YEAR(TODAY())
AND VALUE(DATETIME_FORMAT({End Date}, 'W')) = VALUE(DATETIME_FORMAT(TODAY(), 'W'))
AND {End Date} >= TODAY(),
"THIS WEEK",
IF(
YEAR({End Date}) = YEAR(TODAY())
AND VALUE(DATETIME_FORMAT({End Date}, 'W')) = VALUE(DATETIME_FORMAT(DATEADD(TODAY(), 7, 'days'), 'W'))
AND {End Date} >= TODAY() + INTERVAL '1 WEEK',
"NEXT WEEK",
"FUTURE WEEKS"
)
)
)
),
"No End Date"
)
"
Can someone help me to fix it?