Formula to indicate previous and next months

Jake Gustafson

I'm drawing a blank on how to achieve something to avoid having to manually maintain a sheet.

I've got a main data sheet with new requests getting populated. They have dates associated with them and what I'm doing is pulling them into another sheet to summarize that data based on the month. This piece is working great with the formulas calculating for how many were created in a month, due in a month, and completed in a month.

The piece I need help with is how I can automatically adjust based on the current date and update surrounding cells to use a date in the previous 5-months and next 4-months to show how things have gone and what the forecast is looking like without having to manually adjust any of the data to keep the related Dashboard Graph current.

Paul Newcome

There are a number of ways to do this depending on your existing structure and formulas. Are you able to provide some screenshots?

Jake Gustafson

Here are two screenshots. I don't remember what I was thinking when I originally set this up, but the alert I got from a colleague today was that things were out of whack and probably had been since the beginning of the year, so I must've hard coded some things and I just updated the hard coding for now to get a quick fix out to them. Now I'm looking into how I can make this sustainable and maintainable.

First one is from the top of the sheet, where I've got mostly hard coded info for the data. The cell references are looking at the Year and Month to populate counts based on the full dates in those respective fields from the source sheets.

Second one is what I put at the bottom to use in the Dashboard Graph. What I had tried doing previously was having these 10 months worth of data constantly rotate based on the current month. Unfortunately, like I noted above, I must've had something in there that wasn't refreshing or I maybe just didn't have automations to lock/unlock rows and refresh the TODAY() formula.

I'm not opposed to starting over here as this isn't that complex of data that I'm working with. In fact, I really am only looking for probably 10 months worth of rows (previous 5 months, current month, and next 4 months).

Let me know if that helps @Paul Newcome. I'm excited that you're on the case.

Paul Newcome

Do you need to capture historical data, or is a truly rolling date range acceptable?

Jake Gustafson

Just rolling. The historical data is in the raw data, but we delete records after they're 6-months old to keep metrics fresh based on the 'recent' submissions.

Paul Newcome

In that case, we could use a helper column with manually entered numbers to show how far back and/or forward you want to go (in months) and then an EDate formula to generate the dates. It is a bit of a complex formula and probably honestly over-kill, but it can scale to both ends of Smartsheet's date limitations without error and doesn't require separate formulas on each row.

-5

-4

-3

-2

-1

0

1

2

3

4

5

=IFERROR(DATE(YEAR(Date@row) + ROUNDDOWN((MONTH(Date@row) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH(Date@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH(Date@row) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH(Date@row) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH(Date@row)), 1, 0), IF(MOD(MONTH(Date@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH(Date@row) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH(Date@row) - ABS([Number Of Months]@row) < 1, YEAR(Date@row) - 1, YEAR(Date@row)), IF(MONTH(Date@row) - ABS([Number Of Months]@row) < 1, MONTH(Date@row) + (12 - ABS([Number Of Months]@row)), MONTH(Date@row) - ABS([Number Of Months]@row)), 1))

Jake Gustafson

I'm assuming the [Number of Months] field is what you're indicating is the 'index' column (i.e., -5 through +5), but I don't have anything for Date@row. What I know is TODAY()'s date, then I am interested in those previous 5 months and next 5 months. I don't know how to go about implementing what you've got above because I don't have a date for each month stemming from the current date.

Paul Newcome

My apologies. I got ahead of myself. Replace each

Date@row

with

DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Tip: Drop it into Notepad and use CTRL+H and you can do a find/replace for all of them at once.

Jake Gustafson

That is working great for the 2023 months, but then I run into an error when dropping back to 2022 or jumping to 2024. Maybe I'm missing something yet.

Here's what I'm using:

For the current month (reference point 0) : =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

For the previous/future months : =DATE(YEAR(TODAY()), MONTH(TODAY()) + [Number of Months]@row, 1)

Paul Newcome

You would use that EDate formula above but replace every instance of "Date@row" with just that DATE function indicated.

I went ahead and did a find/replace for you. This should work exactly as is for every month regardless of going into a different year:

=IFERROR(DATE(YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + ROUNDDOWN((MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))), 1, 0), IF(MOD(MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) - ABS([Number Of Months]@row) < 1, YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) - 1, YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))), IF(MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) - ABS([Number Of Months]@row) < 1, MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + (12 - ABS([Number Of Months]@row)), MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) - ABS([Number Of Months]@row)), 1))