Show the next first and last date of a given month from today's date

dafidav

My tech are allocated the same month every year to have their tools calibrated. I want to be able to show the next first and last date of their month from today's date.

Example:

John is assigned January, today's date is 25/12/2022, so I want the first and last date to be: 01/01/2023 and 31/01/2023 shown.

Rob is assigned June, today's date is 15/01/2022, so I want the first and last date to be: 01/06/2022 and 30/06/2022

Please help me with this formula

Jeff Reisman

@dafidav

I'm not understanding how today's date factors in here. Are you using the current date as some sort of baseline for determining what month they are assigned?

If you just want to show the first and last day of the month they need to get their tools calibrated, and you already know what month they are assigned, you can create a lookup chart in a helper sheet.

Column: "MonthNumber" - this is just a list starting with 1 and ending with 12.

Column: "FirstDay" - Date column, listing the first day of each month

Column: "LastDay" - Date column, listing the last day of each month

Back in your main sheet, add a column for Assigned Month, and list the month number each tech is assigned for calibration.

In your first date column: INDEX({Reference to First Day column in helper sheet}, MATCH([Assigned Month]@row, {Reference to MonthNumber column in helper sheet}, 0))

The References to you helper sheet can be created as you type your formula. When you get to the first parentheses after INDEX, follow Smartsheet's prompt to Reference Another Sheet. Go to your helper sheet and select the column you need to reference. Same thing when you get to here "MATCH([Assigned Month]@row," in your formula.

Repeat for Last Day value.

dafidav

@Jeff Reisman I figured the current date is needed for when the next calibration date is the year after

The idea behind First and Last date is so I can build a dashboard to show how many days until a tech is due to have their tools calibrated. Then I also want to set up an automation, so that the tech gets email reminders 2months before the First date, then 1month before the First date to book their tools in for calibration.

Jeff Reisman

Ok, so in your helper sheet, let's construct the firstday and last day values:

FirstDay:

=DATE(YEAR(TODAY()), MONTH(MonthNumber@row), 1)

LastDay:

=DATE(YEAR(TODAY()), MONTH(MonthNumber@row), 31)

Obviously, change the last day to match the month: 31 for January, 28 for February, etc.

Add two new columns, NextFirstDay and NextLastDay. Use the same formulas, except let's add a 1 to the year:

=DATE(YEAR(TODAY())+1, MONTH(MonthNumber@row), 1)

=DATE(YEAR(TODAY())+1, MONTH(MonthNumber@row), 31)

So these will give you next year's values.

Let's put your INDEX/MATCH inside an IF, along with a new INDEX/MATCH for next year:

=IF(MONTH(TODAY()) < [Assigned Month]@row, INDEX({Reference to First Day column in helper sheet}, MATCH([Assigned Month]@row, {Reference to MonthNumber column in helper sheet}, 0)), INDEX({Reference to NextFirstDay column in helper sheet}, MATCH([Assigned Month]@row, {Reference to MonthNumber column in helper sheet}, 0)))

Do the same for the Last Day column.

In English, if the current month is before the assigned month for the user, get this year's date values, otherwise, get next year's date value.

dafidav

@Jeff Reisman I am getting #INVALID DATA TYPE when constructing the below:

FirstDay:

=DATE(YEAR(TODAY()), MONTH([Designated Month #]@row), 1)

LastDay:

=DATE(YEAR(TODAY()), MONTH([Designated Month #]@row), 31)

Jeff Reisman

Make sure your columns are formatted as date-type columns.

Also, [Designated Month #]@row must be a number value. Sometimes what look like numbers are actually text.

If the value is text, it will usually align to the left side of the cell; if it's a number, it will align to the right. If the column type wasn't the problem, you can try using the VALUE function to convert numbers stored as text into numbers stored as numbers:

=DATE(YEAR(TODAY()), MONTH(VALUE([Designated Month #]@row)), 1)

If the above works, you know those month numbers are stored as text.

dafidav

@Jeff Reisman i cant seem to get it to work, even using VALUE

I've done it the old school fashion way, ie manually entering the data

Thanks for all your help!