Converting WEEKNUM to date range?


#1

I’m trying to make my “group by week” formatting easier to understand by showing the date range for the week rather than the week number but can’t seem to get the formula that works in Excel to work in Airtable-- has anyone figured this out? Thank you!


#2

This formula —

DATETIME_PARSE({WeekNum}&'','w')

— will return the first day of the week. You could then use

DATETIME_FORMAT(
    DATETIME_PARSE(
        {WeekNum}&'',
        'w'
        ),
    'M-D-YYYY'
    )&
' – '&
DATETIME_FORMAT(
    DATEADD(
        DATETIME_PARSE(
            {WeekNum}&'',
            'w'
            ),
        6,
        'days'
        ),
    'M-D-YYYY'
    )

to create a date range in the format 'M-D-YYYY – M-D-YYYY'.


#3

Beautiful! Worked like a charm. Thank you so much1


#4

W_Vann-- any idea how to get around this? I’m making a time card view02%20AM


#5

Um… don’t group by a computed field?

This is one of Airtable’s extremely clever and helpful features except when it isn’t. In brief, when you create a new record from within a grouped view, Airtable automatically sets the value of the grouped-by field relative to the point in the view at which you created the record.¹ (It does something similar when you add a record from a filtered view.) The problem comes when you are grouping based on a computed field, as Airtable can’t really reverse engineer the calculation to determine how multiple values should be set. You’ll either have to find a way to group based on some sort of data field — for instance, assuming you are grouping based on the date range calculation, you could instead group on {WeekNum} — or use your grouped view only for data display and enter data on a non-grouped view.


  1. I hope that was somewhat comprehensible, because I can’t think of a better way to phrase it at the moment.

#6

Very helpful answer-- I really only wanted to group by {weeknum} but display the date range on the collapsed field (looks better) but will have to make do with one or the other, it seems.