Bi-Weekly Pay Date

wilbuchanan14

Hello,

Thank you in advance for assisting me. I had created an extensive excel document to manage multiple businesses and have begun switching over to SmartSheet, but have run into some limitations with formulas that I can’t seem to convert with SmartSheet.

I need assistance with creating the next upcoming payroll date based on a bi-weekly schedule. In excel, I used the simple formula of

=CEILING( A1 +8,14)-8 / A1 Being Today’s Date

I have been searching and playing for hours and can not find a solution. Does anyone have an easy formula for creating the next bi-weekly payroll date from today’s date? The last payroll was 6/10/2022 and the next payroll would be 6/24/2022.

Thanks again.

Gareth Jeanes

Hi there,

Maybe I have missed something.

Can you have make your column a date type column, and then have each new row add 14.

See snippet below.

Let me know if this helps, If I have missed anything, please send through an example of what you are aiming to do.

Thanks

G.

wilbuchanan14

Thank you for the response.

Apologies, maybe I should have explained a bit further. I don't want the column filled with all the dates, I just want one cell to change automatically to the next payroll date based on today's date. For example:

In cell A1is today's date. In cell B1, is next payroll date calculated every other Friday. So today's date would be 6/13/2022. I want the cell to say 6/24/2022 until "Today's Date" turns to 6/25/2022, and this will automatically populate to 7/8/2022.

This worked simply with the excel formula mentioned above but due Smartsheet ceiling function not working with dates, I haven't been able to find a solution.

shulac

You have to convert the date to a number using NETDAYS. Here's a solution that I have for finding the beginning of a pay period. I know that 3/15/20 was the first day of a pay period in my organization.

=FLOOR(NETDAYS(DATE(2020, 3, 15), TODAY()), 14) + DATE(2020, 3, 15)