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

Options
dafidav
dafidav ✭✭
edited 02/23/22 in Formulas and Functions

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

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • dafidav
    dafidav ✭✭
    Options

    @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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • dafidav
    dafidav ✭✭
    Options

    @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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • dafidav
    dafidav ✭✭
    Options

    @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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!