Help

Formula to find Previous Monday Date from another Date Field

Topic Labels: Formulas
214 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Lightsourced
4 - Data Explorer
4 - Data Explorer

Hiya! I spent a lot of time googling and trying to find the answer but not having any luck. I need to find a formula that displays the previous Monday date based on a date in another field. Would also love to know what each element does so I can learn a little too rather than straight copy/paste. Thank you so much!

1 Reply 1
dilipborad
8 - Airtable Astronomer
8 - Airtable Astronomer
Hello @Lightsourced 
Please Check this.

 

DATETIME_FORMAT(
    DATEADD(
        {Your Date Field},
        IF(
            WEEKDAY({Your Date Field}) = 0,
            -6,
            IF(
                WEEKDAY({Your Date Field}) = 1,
                -7,
                -WEEKDAY({Your Date Field}) + 1
            )
        ),
        'days'
    ),
    'YYYY-MM-DD'
)​

 

This formula works as follows:

  • It first checks the day of the week for the given date.
  • If the date is a Sunday (0), it subtracts 6 days to get to the previous Monday.
  • If the date is a Monday (1), it subtracts 7 days to find the last Monday.
  • For any other day, it subtracts the weekday number from 1 (-WEEKDAY({Your Date Field}) + 1) to go back to the previous Monday.
  • DATETIME_FORMAT is then used to format the resulting date. You can adjust the 'YYYY-MM-DD' part of the formula to match your preferred date format.

Make sure to replace {Your Date Field} with the actual name of your date field in Airtable. This formula should be added in a new formula field in your Airtable base.
For the any help of Airtable formula you can always check this https://www.airtable.com/universe/expHF9XTWWwAT299z/airtable-formula-playground

👍