# I need to project out 14 days from a given date based on an affirmative answer in another column.

Options

• ✭✭✭✭✭✭
Options

So you need to count how many entries have a date within the past 14 days?

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

Thanks, I will give that a try.

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

Feel free to revisit with your results.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!