How to return 2 week pay period value in timecard app

Ask the community
1

I’m building a simple timecard app with airtable data source for a small construction company. Based on the date entered for a task, I can get the week number and day value for the entry using this airtable formula:

CONCATENATE(“week”,DATETIME_FORMAT({Work Date},“w dd YYYY”))

Based on the {Work Date} entered this returns “week46 we 2024” for instance—the week number, day, and year.
But what I need to do is return the value for our two week pay period instead, ideally something like, “Nov 11-Nov 24 2024” . A value that returned a pay period like, “PayPeriod 26” would also work where the two week period was identified as a number from 1-26 periods for the year.

I’ve looked at related questions here around dates, weeks but wasn’t able to figure out a solution. I’m familiar with basic, intemediate concepts with airtable but continuing to find my way.

Any help, thoughts would be greatly appeciated!

2

I’m sorry, I don’t know how to help you with this one! Quite an advanced formula you need there for a very specific use case.

3

Do the fortnightly pay periods always begin on 01-01-YEAR every year and end on 31-12-YEAR?

4

Hi,
For a PayPeriod system this does the trick:

CONCATENATE(
  "PayPeriod ", 
  FLOOR(
    DATETIME_DIFF({Work Date}, DATETIME_PARSE(
      CONCATENATE(
        "01-01-", 
        YEAR({Work Date})
      ), 
      'MM-DD-YYYY'
    ), 'days') / 14
  ) + 1
)

For the other way (“Nov 11-Nov 24 2024”), I need to check more in details

5

Expanding on @matthieu_chateau’s nice solution

DATETIME_FORMAT({Work Date}, 'MMM M') & '-' & DATETIME_FORMAT({Work Date}, 'MMM') & " "  & 
CONCATENATE(
  FLOOR(
    DATETIME_DIFF({Work Date}, DATETIME_PARSE(
      CONCATENATE(
        "01-01-", 
        YEAR({Work Date})
      ), 
      'MM-DD-YYYY'
    ), 'days') / 14
  ) + 1
) & " " & DATETIME_FORMAT({Work Date}, 'YYYY')
1 Like
6

Thanks so much for the speedy response from both of you! I’ll give it a go tomorrow and let you know how I’ve fared.

7

No they run through the year start each year which I now realize makes assigning 2 week periods 1-26 problematic?

8

Best to check the formula out with some real-world data and if it doesn’t return any results correctly would you give some examples please?

9

Hi Mark, formula works brilliantly for this year but then breaks when we hit 2025. Attached screenshot shows results. Pay Period column is what i have that return each week of the year. PayPeriod column uses the new formula

payperiodtest
payperiodtest2250×1181 226 KB

10

Hi Christian,

Can you try this one:

CONCATENATE(
  DATETIME_FORMAT(
    DATEADD(
      DATETIME_PARSE(
        DATETIME_FORMAT(
          DATEADD(
            {Work Date}, 
            -MOD(DATETIME_DIFF({Work Date}, DATETIME_PARSE("2024-01-01"), 'days'), 7), 
            'days'
          ), 
          'YYYY-MM-DD'
        )
      ), 
      -MOD(
        FLOOR(
          DATETIME_DIFF({Work Date}, DATETIME_PARSE("2024-01-01"), 'days') / 14
        ), 
        2
      ) * 7, 
      'days'
    ), 
    'MMM DD'
  ), 
  "-", 
  DATETIME_FORMAT(
    DATEADD(
      DATEADD(
        DATETIME_PARSE(
          DATETIME_FORMAT(
            DATEADD(
              {Work Date}, 
              -MOD(DATETIME_DIFF({Work Date}, DATETIME_PARSE("2024-01-01"), 'days'), 7), 
              'days'
            ), 
            'YYYY-MM-DD'
          )
        ), 
        -MOD(
          FLOOR(
            DATETIME_DIFF({Work Date}, DATETIME_PARSE("2024-01-01"), 'days') / 14
          ), 
          2
        ) * 7, 
        'days'
      ), 
      13, 
      'days'
    ), 
    'MMM DD YYYY'
  )
)
11

Bingo!!! Thanks so much both of you. I am going to take some time to understand how/why it worked but educating myself is a lot less stressful when I know the answer. :smile: