I need to project out 14 days from a given date based on an affirmative answer in another column.
Answers
-
So you need to count how many entries have a date within the past 14 days?
-
I need to project forward. So if I have 10 people out over the last week, I need to take their first day sick and calculate how many people are out each day for the next 14 days. All 10 out today, 8 tomorrow the next and the next, 7 the next, then 6, etc...does that make sense? Need to be able to provide a count of the number of employees out each day for the next two weeks. Obviously the number changes as more call in sick, but thinking if I can base a formula off their "first day sick" date I should be able to do it. It's just that it's a multiple range and criteria formula and I'm not very good at them yet.
-
So out today would use TODAY(), out tomorrow would use TODAY(1), the next day is TODAY(2), etc, etc...
Your formula would end up looking something along the lines of:
=COUNTIFS(First:First, DATE(YEAR(@cell), MONTH(@cell), DAY(@cell)) + 14 >= TODAY())
If needed, you can replace TODAY() with another date or a cell reference.
-
Thanks, I will give that a try.
-
Happy to help! 👍️
Feel free to revisit with your results.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
