Skip to main content

Repeating Friday Date Every 2 Weeks

  • July 21, 2022
  • 2 replies
  • 42 views
D
Forum|alt.badge.img+3

Looking to convert this excel formula into an Airtable formula.

=TODAY()+7-WEEKDAY(TODAY(),16)

Details
We run payroll every 2 weeks, so we’d like to send a reminder on the Friday that timecards are due. So, if today is 7/21, the date in the cell should read 7/29 - until it passes. Then the next date should read 8/12.

The above formula does that in Excel, but I’m at a loss of how to convert it.

2 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hey Daniel, I’ve put something together here for you

The screenshot above is set up as demo data to break down the logic and to show the expected results based on the different Today values, and the actual formula can be found in the field Actual Formula as well as attached below

Pretty sure there’s a simpler way to do this but eh, this is what I could come up with hah

IF(
  MOD(
    DATETIME_DIFF(
      TODAY(), 
      DATETIME_FORMAT(
        '01/07/2022', 
        'DD/MM/YYYY'), 
        'days'
      ), 
      14
    ) = 0,
    TODAY(),
    DATEADD(
      TODAY(), 
      (14 - MOD(
              DATETIME_DIFF(
                TODAY(), 
                DATETIME_FORMAT(
                  '01/07/2022', 
                  'DD/MM/YYYY'
                ), 
                'days'
              ), 
              14
            )
          ), 
        'days'
      )
)
Adam, Airtable Consultant - thetimesaving.company
D
Forum|alt.badge.img+3
  • Daniel_R
  • Author
  • New Participant
  • July 22, 2022

Hey Daniel, I’ve put something together here for you

The screenshot above is set up as demo data to break down the logic and to show the expected results based on the different Today values, and the actual formula can be found in the field Actual Formula as well as attached below

Pretty sure there’s a simpler way to do this but eh, this is what I could come up with hah

IF(
  MOD(
    DATETIME_DIFF(
      TODAY(), 
      DATETIME_FORMAT(
        '01/07/2022', 
        'DD/MM/YYYY'), 
        'days'
      ), 
      14
    ) = 0,
    TODAY(),
    DATEADD(
      TODAY(), 
      (14 - MOD(
              DATETIME_DIFF(
                TODAY(), 
                DATETIME_FORMAT(
                  '01/07/2022', 
                  'DD/MM/YYYY'
                ), 
                'days'
              ), 
              14
            )
          ), 
        'days'
      )
)

Thanks Adam. It certainly works. Seems a bit unfortunate have to go this long way to replicate an otherwise simple Excel formula, but it does work.

TheTimeSavingCo
Terms & ConditionsAccessibility statement