How to look up if today's date falls in another sheet's date range & return another cell value @ row



I have created a calendar sheet with numbered weeks in a quarter (1-13) start and end dates for each week. I want to write a formula in a separate metric sheet that allows me to use today's date to search the calendar sheet for the correct week.

Below is what my calender sheet looks like. So if today is Feb 10, 2022, the formula in my metrics sheet would search the calendar sheet and return back the value for Week in Q, which in this case would be: "Week 10".

Can you please help me write this?

Below is what I go so far, but not sure I am thinking this through the right way.



=IFS(TODAY() > DATE({Start Date})@row, TODAY() < DATE({End Date})@row,


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!