Checkbox formula for this month and last month

Options
Adamcain62
Adamcain62 ✭✭✭✭

I have 2 checkbox columns with column formulas. One column to identify if the line was Created This Month? and one to identify if the line was created Last Month?. It seems to work just fine, unless the line was created after 4:00 PM (or maybe 4:30 PM)... then it works the opposite...

This Month? formula: =IF(AND(MONTH(TODAY()) - MONTH(Created@row) = 0, YEAR(TODAY()) = YEAR(Created@row)), 1, "")

Last Month? formula: =IF(AND(MONTH(TODAY()) - MONTH(Created@row) = 1, YEAR(TODAY()) = YEAR(Created@row)), 1, "")

Image below: This Month? Formula


Image below: Last Month? Formula


Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Adamcain62 Let me guess - you're on the west coast, Pacific Standard Time, UTC-8, right? While the created and modified date/time stamps look like they're in PST, they're not. They're stored in UTC. So 2/28/23 at 4:00PM is really 3/1/23 at 12:00AM.

    So here's what you do to make this work: Create a workflow automation, trigger when a row is added, that Records the date in another date-type column, call it "CreatedDate". This will record the date as it is currently in the sheet owner's time zone. So 2/28/23 at 4:01PM is recorded as 2/28/23, not 3/1/23. Then use that CreatedDate column in your formulas instead.

    (I just happened to rant about Smartsheet's screwy timestamping last week!)

    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!

  • Adamcain62
    Adamcain62 ✭✭✭✭
    Answer ✓
    Options

    What I ended up doing is leveraging another column, and used a formula there to convert the system Created column to just a date, using a Date column type. ChatGPT gave me this to use ; )


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Adamcain62 It's crazy, right?

    To fix the existing rows, copy the date values into another sheet or into Excel. Remove the formula from the date column. Save. Copy the dates back into your sheet (500 rows at a time 😔 ) and manually fix the those on rows created after 4pm.

    Then set up you Record a Date automation, using "when a row is added" as the trigger, to put the date into that date field for all future rows.

    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Adamcain62 in case you're interested, here's the formula to extract the visible date from the Created column into an actual date:

    =DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

    The benefits of having the automation record the date for you instead of using the formula:

    You can change it manually after the fact if you want, and;

    Less processing overhead on the sheet, which helps a lot with load, save, and refresh times if it's a large sheet.

    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!

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    edited 03/02/23
    Options

    Create another column named Date (column type date) and put the formula

    =Created@row

    Right-click on the cell with the formula and click "Convert to Column Formula"

    Then update your formula to

    This Month? formula: =IF(AND(MONTH(TODAY()) - MONTH(Date@row) = 0, YEAR(TODAY()) = YEAR(Date@row)), 1, "")

    Last Month? formula: =IF(AND(MONTH(TODAY()) - MONTH(Date@row) = 1, YEAR(TODAY()) = YEAR(Date@row)), 1, "")

    The issue is with the column type that Created is so by converting it to the proper date column type it should solve your issues.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Adamcain62 Let me guess - you're on the west coast, Pacific Standard Time, UTC-8, right? While the created and modified date/time stamps look like they're in PST, they're not. They're stored in UTC. So 2/28/23 at 4:00PM is really 3/1/23 at 12:00AM.

    So here's what you do to make this work: Create a workflow automation, trigger when a row is added, that Records the date in another date-type column, call it "CreatedDate". This will record the date as it is currently in the sheet owner's time zone. So 2/28/23 at 4:01PM is recorded as 2/28/23, not 3/1/23. Then use that CreatedDate column in your formulas instead.

    (I just happened to rant about Smartsheet's screwy timestamping last week!)

    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!

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

    @Devin Lee Just using =Created@row doesn't fix the issue if the Created date/time places it in the next day based on UTC/GMT time.

    Here's one created on 2/21/23 at 11:37pm. I'm in Eastern Standard Time (UTC-5).

    =[Start Date]@row pulls the date as it is in UTC.

    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!

  • Adamcain62
    Adamcain62 ✭✭✭✭
    Options

    Thanks both! yea, I figured that was too coincidental to not have something to do with it - pacific time zone here. Good to know!

  • Adamcain62
    Adamcain62 ✭✭✭✭
    Answer ✓
    Options

    What I ended up doing is leveraging another column, and used a formula there to convert the system Created column to just a date, using a Date column type. ChatGPT gave me this to use ; )


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

    Unfortunately, I don't think that's going to work for you. It's still going to pull the date in UTC time from the system Created date:

    The only way to still use the value in the Created date and get accurate results is to extract the values as text, tack "20" onto the 23 for the year, and then plug those into your DATE formula. That's a very messy process. It is far easier to just use the Record a Date automation.

    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!

  • Adamcain62
    Adamcain62 ✭✭✭✭
    Options

    Thank you very much for the continue effort on this Jeff! It appeared to work, because it is checking appropriately based on the date column... but the date column is converting anything on 2/28 after 4 PM to 03/01. I didn't catch that. I wanted to post this for posterity to delegitimize my previous response. ; )


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Adamcain62 It's crazy, right?

    To fix the existing rows, copy the date values into another sheet or into Excel. Remove the formula from the date column. Save. Copy the dates back into your sheet (500 rows at a time 😔 ) and manually fix the those on rows created after 4pm.

    Then set up you Record a Date automation, using "when a row is added" as the trigger, to put the date into that date field for all future rows.

    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Adamcain62 in case you're interested, here's the formula to extract the visible date from the Created column into an actual date:

    =DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

    The benefits of having the automation record the date for you instead of using the formula:

    You can change it manually after the fact if you want, and;

    Less processing overhead on the sheet, which helps a lot with load, save, and refresh times if it's a large sheet.

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!