Checking a Date Range

Hello,

I am trying to figure out which type of formula I would use to allow my project plan to display the correct information for a certain date. My attempt that I keep doing is to use an IF formula to compare the date ranges in the Column with the Today function to see if any are less than or equal to Today's date. If they are less than or equal to today's date then it would simply output the description for that specific range. My problem is having it figure out which is the correct date range to pull the information for. Any help on this would be greatly appreciated.

Answers

  • Hi there, would you mind showing the formula your attempting to use?

  • Hello Graham, I don't have a formula currently I have been trying to either use an if/and function. I will try to give a very good description of how the data is that I am pulling from. I have a range of dates that go through 2021. I want to use the Today() function to go through this range of dates until it finds the range it is supposed to be in. So this week the range would be 12/13-12/19. I would need the function to go through this range of dates and once it figures out the correct range to output the description for that specific range.

  • Hi Peter,

    do you need to have the 'Week Range'? Would just the week number be OK for you?

    If you are to use an IF statement, there could possibly be 52 nested? It's quote a lot?

    if the Week Number is OK - you can use - WEEKNUMBER( date ), this will return the week number from your date column?

    Thanks

    Graham

  • Unfortunately the ranges aren't exactly a week long and vary. I was just providing a rough date so that you could get an idea of what I was trying to accomplish. I am guessing I am going to write an IF(AND()) statement to check each two dates to see if Today() fits in between that range, and if so to output the correct answer, else continue on doing IF(AND()) until it finds the correct date range.

  • I'm afraid to my knowledge that is the way to do it, and it will possibly be a long formula until it finds the correct range. Sorry I couldn't be of more help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!