Deduce Week Number (of 52) from Date Field

Topic Labels: Formulas
Solved
5395 4
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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

1 Solution

Accepted Solutions
4 - Data Explorer

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)

4 Replies 4
4 - Data Explorer

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)

6 - Interface Innovator

Lina, you’re an Angel. I’ll give this a shot when I’m back at a computer. Much appreciated.

6 - Interface Innovator

Worked brilliantly. Exactly what I needed. Thanks again.

6 - Interface Innovator

Hi!

Nice shot!

But I need to be a bit further, and I am facing errors.

This is my formula:

"

IF(
{End date},
IF(
VALUE(DATETIME_FORMAT({End date}, 'W')) < VALUE(DATETIME_FORMAT(TODAY(), 'W')),
"PAST",
IF(
VALUE(DATETIME_FORMAT({End date}, 'W')) = VALUE(DATETIME_FORMAT(TODAY(), 'W')),
IF(
{End date} >= TODAY(),
"THIS-WEEK",
"FUTURE"
),
IF(
VALUE(DATETIME_FORMAT({End date}, 'W')) = VALUE(DATETIME_FORMAT(DATEADD(TODAY(), 7, "days"), 'W')),
"NEXT WEEK",
"FUTURE"
)
)
),
"No End Date"
)

"

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?