Review of data for 90 day consecutive no miss

I am working on a time off accrual form for HR. per contract after June 1st of every year if an employee does not have an unexcused absence in 90 days they earn another personal day.

I have an accrual sheet that calculates vacation by hire date, personal days available at start of every calendar year and additional day after June 1st, but I can't figure the calculation to add the earned day (total of 4 to be earned).

I have another sheet for attendance that have a column for each unexcused absence up to 5, each is a date. Dates are manually entered.

How can I run it so it will review the attendance tracker for 90 days without an unexcused after June 1st and output 1 in the earned column so it can be added to total personal accrual?

Find more posts tagged with

Accepted answers

All comments

Jeff Reisman

To check your attendance sheet from the accrual sheet, you'll want to add a column in the accrual sheet to count unexcused absences that meet the date criteria. Throw it inside an IF to only count it if the current date is greater than or equal to Jun 1st.

=IF(TODAY() >= DATE(YEAR(TODAY()), 6, 1), COUNTIFS({Reference Employee name column}, EmployeeName@row, {Reference all 5 absence date columns}, AND(@cell < DATE(YEAR(TODAY()), 6, 1), @cell >= DATE(YEAR(TODAY()), 3, 1))), "")

Put this on each employee line, you'll get the count of unexcused absences between March 1 and June 1 (past 3 months). If you specifically want to do 90 days, instead of 3 months, change the date range just a bit:

AND(@cell < DATE(YEAR(TODAY()), 6, 1), @cell >= (DATE(YEAR(TODAY()), 6, 1) - 90)

So if you really just want to to check each quarter of the current year, but not start accruing until June 1 (so on June 1, if you've had 0 unexcused absences for the year you accrue 2 days, then another on Oct 1, and one more on 12/31, provided no unexcused absences at all...) then you'll want to pull counts for all 4 date ranges, and add them together:

=IF(TODAY() >= DATE(YEAR(TODAY()), 6, 1), (COUNTIFS({Reference Employee name column}, EmployeeName@row, {Reference all 5 absence date columns}, AND(@cell < DATE(YEAR(TODAY()), 6, 1), @cell >= DATE(YEAR(TODAY()), 3, 1)))) + (COUNTIFS({Reference Employee name column}, EmployeeName@row, {Reference all 5 absence date columns}, AND(@cell < DATE(YEAR(TODAY()), 3, 1), @cell >= DATE(YEAR(TODAY()), 1, 1)))) + (COUNTIFS({Reference Employee name column}, EmployeeName@row, {Reference all 5 absence date columns}, AND(@cell < DATE(YEAR(TODAY()), 9, 1), @cell >= DATE(YEAR(TODAY()), 6, 1)))) + (COUNTIFS({Reference Employee name column}, EmployeeName@row, {Reference all 5 absence date columns}, AND(@cell < DATE(YEAR(TODAY()), 12, 31), @cell >= DATE(YEAR(TODAY()), 9, 1)))), "")

(This formula will automatically reset the date criteria to the new year on 1/1/23.)

sanscritt

Jeff, very much appreciated! i will work through this and let you know how it works for me.

sanscritt

I am consistently getting an incorrect argument set error.

As an update I did get clarification on the contract language, an additional personal day is added after June 1 if there are no unexcused absences for 90 days so, basically, on Sept first it will look back to June to see if there were any missed days and add 1 day if no missed time so it is a one time check.

Just to clarify is the @cell part of the formula or is it supposed to reference a specific cell and if so, which cell should I be referencing?

As always your help is greatly appreciated.

Jeff Reisman

@sanscritt

"@cell" is part of the formula.

Can you share the formula you are using?

#INCORRECT ARGUMENT SET usually indicates mismatched range sizes or a problem with a function missing an argument or extra functions in the argument.

Formula Error Messages | Smartsheet Learning Center

Functions List | Smartsheet Learning Center

Search for the Smartsheet Formula Examples template to add to your Smartsheet folder, it's free and contains helpful examples of every function.

sanscritt

When I enter the formula as below it does not give me an error:

=IF(TODAY() >= DATE(YEAR(TODAY()), 9, 1), COUNTIFS({Employees}, Employee@row, {Attendance & Sick Time tracking Range 2}, AND(@cell > DATE(YEAR(TODAY()), 6, 1), @cell <= DATE(YEAR(TODAY()), 9, 1))), "")

But I tried to manipulate to see if it works properly without waiting until June and I get the incorrect argument set (see below). I tried to change the date to be greater than Jan and look between Jan and March as I know there have been several unexcused.

The {Employees} references the attendance sheet, Employees@row is on the accrual sheet and the {Attendance & Sick Time tracking Range 2} is pulling the 5 point columns from the attendance sheet.

=IF(TODAY() >= DATE(YEAR(TODAY()), 1, 1), COUNTIFS({Employees}, Employee@row, {Attendance & Sick Time tracking Range 2}, AND(@cell > DATE(YEAR(TODAY()), 1, 1), @cell <= DATE(YEAR(TODAY()), 3, 1))), "")

Jeff Reisman

What happens if you just put the the COUNTIFS portion in?

=COUNTIFS({Employees}, Employee@row, {Attendance & Sick Time tracking Range 2}, AND(@cell > DATE(YEAR(TODAY()), 1, 1), @cell <= DATE(YEAR(TODAY()), 3, 1)))

sanscritt

I still get incorrect argument set using just the countifs.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320725#Comment_320725

Make sure for your ranges that your ranges contain the same data type as the data you're searching for, and that your parentheses color-coding matches up in your formula. Match up the starting and ending parentheses for each argument in your formula.

sanscritt

I have double and triple checked, even copy and pasted what you had and verified the cell references and still get incorrect argument set.

=COUNTIFS({Employees}, Employee@row, {Attendance & Sick Time tracking Range 2}, AND(@cell > DATE(YEAR(TODAY()), 1, 1), @cell <= DATE(YEAR(TODAY()), 3, 1)))

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.