Date Range Formula Assistance

jesrom18

Is there a formula that moves entries to the next month if it falls within a certain date range? For instance, entries from February 16th - March 15th would be placed in the April cohort. I would like this formula to be permanent each month.

Kerry St. Thomas

For me, time intelligence isn't very nice to play with in Smartsheet. Without a calendar table, I feel that it's kind of tragic doing math on dates. So I heartily recommend you build a calendar table as a sheet in Smartsheet so that you have something to look up against. (I have one that uses the ETL of Power Query in Excel to accept my inputs for a date range, and I use Data Shuttle to load that info into a sheet.)

If you don't want to do that, you are more than welcome to give this a try: in your example, the return isn't "April" but rather the number 4. You could conceivably make a lookup table where 4 = April and so on. I'm certain there's a more elegant way to do this than what I came up with. But like I said… tragic. And it's late in the day so my brain wants to be done. 😂

=IF(IF(DAY(Date@row) > 15, MONTH(Date@row) + 2, MONTH(Date@row) + 1) > 12, IF(DAY(Date@row) > 15, MONTH(Date@row) + 2, MONTH(Date@row) + 1) - 12, IF(DAY(Date@row) > 15, MONTH(Date@row) + 2, MONTH(Date@row) + 1))