Help

Deduce Week Number (of 52) from Date Field

Topic Labels: Formulas
Solved
Jump to Solution
5395 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Stephen
6 - Interface Innovator
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

Screenshot 2022-04-01 at 16.08.03

1 Solution

Accepted Solutions
Lina_Clark
4 - Data Explorer
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)

See Solution in Thread

4 Replies 4
Lina_Clark
4 - Data Explorer
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)

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

Worked brilliantly. Exactly what I needed. Thanks again.

NLOIA
6 - Interface Innovator
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?