Checkbox formula for this month and last month

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

Jeff Reisman

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

Adamcain62

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

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

Jeff Reisman

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

Devin Lee

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

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

Jeff Reisman

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

Adamcain62

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

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

https://community.smartsheet.com/discussion/comment/365383#Comment_365383

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.

Adamcain62

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

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

Jeff Reisman

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