I need to edit this Index(Collect) and add if statement

FoxFoxa

Hello Everyone,

I have built this formula before and now I need to edit this formula to include:

If the date created is greater then Payroll Deadline then return PP+1, if not find PP

=INDEX(COLLECT({Payroll End date Range 1}, {Payroll End date Range 2}, [Last Day Worked]@row >= @cell, {Payroll End date Range 3}, [Last Day Worked]@row <= @cell), 1)

jmyzk_cloudsmart_jp

Hi @FoxFoxa

You could use a formula like this;

=INDEX(COLLECT({Payroll End date Range 1}, {Payroll End date Range 2}, [Last Day Worked]@row >= @cell, {Payroll End date Range 3}, [Last Day Worked]@row <= @cell), 1) + IF([Created Date]@row > INDEX(COLLECT({Payroll End date Range 4}, {Payroll End date Range 2}, [Last Day Worked]@row >= @cell, {Payroll End date Range 3}, [Last Day Worked]@row <= @cell), 1), 1, 0)

In this formula, {Payroll End date Range 4} range refers to the "Payroll Deadline" column.

Please review the demo Dashboard and change the "Payroll Deadline" in the "Pay Period =20" row to check if the formula works properly.

(Change from 10/24/23 to 10/19/23, for example.)

https://app.smartsheet.com/b/publish?EQBCT=95bfcd1abe834d1d9214790c44e0d74d

Note on Created Date:

The smartsheet system column, "Created Date" or "Created," has the value of GMT or UTC, whereas the display value is based on the time zone setting of your "Personal Setting."😅

So if you compare the "Payroll Deadline" date with Created or DATEONLY(Created), you sometimes get the wrong answer depending on your timezone and time of the row creation.

So, I use the following formula to get the date part display value of "Created Date" or "Created." 

 =DATE(YEAR(Created@row), MONTH(Created@row), VALUE(MID(Created@row, 4, 2)))