Updates Occur After Date Specified is Reached

I need for updates to Profile sheet to pull data from Exception sheet only when the date is reached based on the employee #.

Scenario: An employee wants to increase their 401K contribution by $100 every quarter. Profile has Benefit Effective Date: 4/1/22, 401K Contribution: $100.

Exception sheet has 3 exceptions submitted: 1) benefit effective date 7/1/22, 401K contribution $200. 2) benefit effective date 10/1/22, 401K contribution $300; 3) benefit effective date 1/1/23, 401K contribution $400.

I need the profile sheet Benefit Effective Date to update from the Exception sheet on 7/1/22 with the date 7/1/22 so the benefit column will pull the $200. This is needed so payroll can begin making the correct deduction.

Find more posts tagged with

Human Resources

Formulas

Automations

Workflows in Smartsheet

Cell linking

Accepted answers

Genevieve P.

Hi @SMullen

Thank you for clarifying and for adding your formula!

In this case I would use the MAX Function instead of INDEX to bring back the most recent date, based on your criteria in the COLLECT function:

=MAX(COLLECT({Payroll Exception Sheet 401K Eff Date}, {Payroll Exception Sheet Emp ID}, [Emp ID]@row, {Payroll Exception Sheet 401K Eff Date}, <=TODAY()))

Let me know if this gives you the data you're looking for.

Cheers,

Genevieve

All comments

Genevieve P.

Hi @SMullen

Do you already have the formula set up to pull the $200 based on the Employee # and the Date, and you're just looking for a way to update the Date cell to the correct one?

If so, I would set up a Record a Date workflow that update your Date column on specific date:

Screen Shot 2022-06-20 at 12.10.05 PM.png

If I've misunderstood the question, I think it would be helpful to see screen captures of your two sheets, but please block out sensitive data (or copy the sheets and use example data instead).

Cheers,

Genevieve

SMullen

@Genevieve P.

I need the formula in the Profile Benefit Effective Date cell to pull the transaction from the Exception sheet when the date is reached but not before. This will enable the new benefits to take effect on 7/1/22 in this example.

The formula tried: =INDEX(COLLECT({Payroll Exception Sheet 401K Eff Date}, {Payroll Exception Sheet Emp ID}, [Emp ID]@row, {Payroll Exception Sheet 401K Eff Date}, <=TODAY()), 1) The issue: COLLECT pulls the 1st instance of the criteria which means the subsequent dates are not pulled since 4/1/22 is the 1st instance recorded and is less than today.

Any formula suggestion that can pull the most recent date including today not beyond today?

Sheila

Genevieve P.

Hi @SMullen

Thank you for clarifying and for adding your formula!

In this case I would use the MAX Function instead of INDEX to bring back the most recent date, based on your criteria in the COLLECT function:

=MAX(COLLECT({Payroll Exception Sheet 401K Eff Date}, {Payroll Exception Sheet Emp ID}, [Emp ID]@row, {Payroll Exception Sheet 401K Eff Date}, <=TODAY()))

Let me know if this gives you the data you're looking for.

Cheers,

Genevieve

SMullen

@Genevieve P.

Thank you! It works exactly as needed!

Cheers,

Sheila

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.