Trying to calculate pay periods where pay date is always on the 15th and end of month

[Gina_Fernandez]

Hello, somewhat new to airtable and using the Payroll Timesheet Template available in the template library. However, I’m having a bit of trouble figuring out how to properly calculate the pay periods as they’re not every 15 days (like in the template), but rather the 15th and the last day of the month. It currently takes the
Start Date formula DATEADD({Start of Pay Period 1},({Pay Period Number}*14)-14,'days')
and the
End Date formula* DATEADD({Start Date},13,'days')
to calculate the
Pay Date DATEADD({Start Date},13,'days')

I am wondering how best to calculate the Start and End Dates based on the Pay Dates always being the 15 and the EOM (30th or 31st). I’ve been playing with different formulas all day and have figure out how to calculate EOM at least, but not how to specify the 15th and EOM as the pay dates and at this point it’s all starting to look like hieroglyphics to me.

If anyone has come across a solution or similar use case and could shed some light on this, I would be deeply grateful.

[TheTimeSavingCo]

Hi Gina, could you create a table where you manually set up all the data to exactly how you’d want it to look?

Like, I get that you want it to all be automatically calculated via formulas and such, but if you could do it manually with single line text fields once it would give me a good idea of what you’re trying to achieve and I could make something for you from there

(I tried to wrap my mind around the Payroll Time Sheet template, but quickly gave it up as a bad job as I realized I didn’t even fully understand what your intended output looked like ha)

[ScottWorld]

The quickest way to create a formula that equals the last day of a current month is to subtract 1 day from the first day of the following month.

Here’s one way of calculating the last date of a month, although there are several different ways of doing this, so somebody might have a shorter formula that does the same exact thing:

DATEADD(
DATETIME_PARSE(
MONTH(DATEADD({Your Date Field},1,'month')) & "/1/" & YEAR(DATEADD({Your Date Field},1,'month')),
'M/D/YYYY'),
-1,'day')

[KaraMG]

Hi, this question hasn't really been answered - I'm in the same boat as Gina and I would love any advice!

The template uses a "pay schedule" tab that uses these formulas. I can't really copy/paste what Scott replied above.