formula help.
HI all
I am running a holiday calendar on smartsheet and looking to increase its functionality.
I'm currently trying to write a formula using a countifs were if employee A puts a holiday request through it checks all of employee A previous entries to see if it clashes with a previous entry.
the formula i'm using isn't right and any suggestions on where to go with it would be great as my tiny mind is going to explode.
The columns i'm checking are start date, end date and employee name.
current formula
=COUNTIFS([EMPLOYEE NAME]:[EMPLOYEE NAME], [EMPLOYEE NAME]@row, [START DATE]:[START DATE], >=[START DATE]@row, [START DATE]:[START DATE], <=[END DATE]@row)
Best Answer
-
Very close. Try this...
=COUNTIFS([EMPLOYEE NAME]:[EMPLOYEE NAME], [EMPLOYEE NAME]@row, [END DATE]:[END DATE], >=[START DATE]@row, [START DATE]:[START DATE], <=[END DATE]@row)
This will give you the count of how many rows for that employee have any kind of date overlap at all. 1 means there is no overlap (as the current row will still fall within the criteria in the formula).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Very close. Try this...
=COUNTIFS([EMPLOYEE NAME]:[EMPLOYEE NAME], [EMPLOYEE NAME]@row, [END DATE]:[END DATE], >=[START DATE]@row, [START DATE]:[START DATE], <=[END DATE]@row)
This will give you the count of how many rows for that employee have any kind of date overlap at all. 1 means there is no overlap (as the current row will still fall within the criteria in the formula).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
you sir are what you would call a "LEGEND". I could not work out what was wrong.
thanks.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!