PTO Track How to Calculate a specific number (PTO Hrs Earned) on a specific date (Pay day)

Our company calculates the number of PTO hours earned, based on number of years in service and pay period depending on years of service; less than 5 years (7.08), greater than 5 years (8.62). I only want the earned PTO hours added to current PTO hours on Pay Day. Pay Day is every other Friday.

I have set up two sheets and a flow. Sheet 1 is Team Member Information. Sheet 2 is Payroll Information. The flow looks at Today's date and updates current date.

A formula uses the MATCH function and compares Current Date to Pay Day in Payroll Information. If there is a match it returns the PTO hours for the pay period. If there is not a match it returns #NO MATCH. I really need and want it to return 0 when there is not a match (indicating not a pay day no hours earned).

I also need it to store previous hours which consist of current hours, minus approved PTO hours.

Current formula only works on a Pay Day

=(IF([Years Employed]@row >= 5, INDEX({Payroll Information 02-06 | Five Yrs or More}, MATCH([Today's Date]@row, {Payroll Information 02-06 | Pay Day}, 0)), INDEX({Payroll Information 02-06 | Less than Five Yrs}, MATCH([Today's Date]@row, {Payroll Information 02-06 | Pay Day}, 0))))

It a match is not found (any other day than pay day) it returns #NO MATCH, it is correct because if it isn't a payday it won't find a match. Help please. I have been fooling with this for days, and days... I am new to Smartsheets and this is kicking my tail... I need a libation!!

Find more posts tagged with

Accepted answers

KPH

Hi @Catly

Dont turn to drink! You’ve got this! Honestly, you’ve done the hard part.

The last little thing you need is an IFERROR function. Pop that around your function and you can change #NO MATCH to 0 (or anything else you want).

The syntax is =IFERROR(your formula, value if error)

So, in your case

=IFERROR(IF([Years Employed]@row >= 5, INDEX({Payroll Information 02-06 | Five Yrs or More}, MATCH([Today's Date]@row, {Payroll Information 02-06 | Pay Day}, 0)), INDEX({Payroll Information 02-06 | Less than Five Yrs}, MATCH([Today's Date]@row, {Payroll Information 02-06 | Pay Day}, 0))),0)

All comments

KPH

Hi @Catly

Dont turn to drink! You’ve got this! Honestly, you’ve done the hard part.

The last little thing you need is an IFERROR function. Pop that around your function and you can change #NO MATCH to 0 (or anything else you want).

The syntax is =IFERROR(your formula, value if error)

So, in your case

Catly

Hero Status for you!! It worked. Thank you, so much. I have tried to do that too, but received Incorrect Argument error and or Unparsarable. Let me copy and paste your solution and try. You are the BEST. Thank you for helping me.

KPH

You did it!

The problem with IFERROR is remembering the thing to display is there is an error. Sometimes people add the start, then by the time they have entered the formula, forget the bit at the end.

Good to know you are sorted.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.