Action based on System-Generated Modified Date/Time

@SPark

Is there a forumla I can create that will check a box based on the auto-generated timestamp?

Screenshot:

I am looking to check the AM/PM box based on entry time (anything before 12PM would check the AM box and anything after 12PM would check the PM box).

Paul Newcome

What do you mean by "it did not work"? Are you getting an error or unexpected results?

Let's try something different...

=IF(FIND("A", Modified@row) > 0, 1)

=IF(FIND("P", Modified@row) > 0, 1)

Paul Newcome

Ah. Those were for converting the times for a display. The solution you are gong to want involves using an IF statement to determine if it is pm and then a MID statement to simply look at the hour. We pull the date into another date type column and use the IF to say that if it meets the criteria, add or subtract one day depending on your specific needs. It is not nearly as complicated as time manipulation. Your formula in a single date type column is going to look something along the lines of...

=DATEONLY([Created (Date)]@row) - IF(AND(PM@row = 1, VALUE(MID([Created (Date)]@row, 10, FIND(":", [Created (Date)]@row) - 10)) >= 5), 1)

Since we already have the PM column, we just use that to check for that, and then we use this part:

VALUE(MID([Created (Date)]@row, 10, FIND(":", [Created (Date)]@row) - 10))

to pull the hour and convert it to a numerical value. If that number is greater than or equal to 5, we subtract a day from the incorrect date that is being pulled by the DATEONLY function.

Then you would use this helper column instead of the created column in your COUNTIFS.

Paul Newcome

Try...

=CONTAINS("A", Modified@row)

and

=CONTAINS("P", Modified@row)

@SPark

Hi @Paul Newcome !!

It unfortunately did not work.. the system generated columns always give me grief.

Paul Newcome

What do you mean by "it did not work"? Are you getting an error or unexpected results?

Let's try something different...

=IF(FIND("A", Modified@row) > 0, 1)

=IF(FIND("P", Modified@row) > 0, 1)

@SPark

The =CONTAINS("A", Modified@row) forumla did not give me an error, however, it also did not give me any results (i.e., the box did not check).

The 2nd formula =IF(FIND("A", Modified@row) > 0, 1) did work!!

@Paul Newcome you're my smartsheet hero.

@SPark

@Paul Newcome if you have time would you be able to explain to me why the 2nd would work and the 1st would not produce the same outcome? I tried a couple myself before posting on the community and I couldn't figure it out-- but I was only doing versions of IF. Any insight is appreciated!!

Paul Newcome

Honestly I am not exactly sure why. The CONTAINS function produces a true/false result. As a standalone you can normally use it as above to check a box based on whether or not specific text is found in a cell (or range of cells).

For example...

I am not sure why the CONTAINS didn't work because I have used it in the past looking for "a" vs "p" in Time Calculations but it was within a larger formula.

I also tested nesting the CONTAINS in an IF to test that, but that didn't work either.

The FIND function seems to have picked it up though.

I can't tell you exactly why one worked and the other didn't though. I just hope I don't have to go through and redo all of my time related formulas to remove the CONTAINS function. They WERE previously working...

@SPark

I have no idea what I am missing but any ideas/thoughts would be appreciated!!

My goal is to pull a report that shows today's entries. Sheet B (below) is marking AM/PM based on the help @Paul Newcome gave me in the above comments-- and then Sheet A is counting the AMs and PMs based on the formula below. The issue is Sheet A is still counting entries from yesterday and I'm not sure what I'm missing. I also have this thread https://community.smartsheet.com/discussion/68964/count-if-on-system-generated-column#latest where I adjusted the working hours/days to 24/7.

(Sheet A)

The Formula for this is: =COUNTIFS({Store}, Store@row, {Created}, TODAY(), {PM_}, 1)

Which is pulling from this sheet: (Sheet B)

Where the formula reads: =IF(FIND("P", [Created (Date)]@row) > 0, 1)

@SPark

ALSO- when I apply a filter with the condition as created date as today-- it shows those two entries from last night. So it's something with the system generated date. (I think?)

Paul Newcome

It may be a timezone thing. I have seen this a few times recently here in the community. Are you able to pinpoint which hour the sheet starts pulling for "today"? Is it anything after 6pm, or 7pm, etc?

@SPark

@Paul Newcome It looks like 5PM from the day prior.

Paul Newcome

Ok. Let me dig through my notes and past comments here in the Community. I will try to find the solution we used before for this same thing.

@SPark

I was able to find some threads on this but the work arounds seemed really complicated.. added columns with hours, minutes, etc. and extremely long formulas..

If that's the case @Paul Newcome no need to dig through your notes! I can make due by updating manually. I appreciate your help and insight as always.

Paul Newcome

Ah. Those were for converting the times for a display. The solution you are gong to want involves using an IF statement to determine if it is pm and then a MID statement to simply look at the hour. We pull the date into another date type column and use the IF to say that if it meets the criteria, add or subtract one day depending on your specific needs. It is not nearly as complicated as time manipulation. Your formula in a single date type column is going to look something along the lines of...

=DATEONLY([Created (Date)]@row) - IF(AND(PM@row = 1, VALUE(MID([Created (Date)]@row, 10, FIND(":", [Created (Date)]@row) - 10)) >= 5), 1)

Since we already have the PM column, we just use that to check for that, and then we use this part:

VALUE(MID([Created (Date)]@row, 10, FIND(":", [Created (Date)]@row) - 10))

to pull the hour and convert it to a numerical value. If that number is greater than or equal to 5, we subtract a day from the incorrect date that is being pulled by the DATEONLY function.

Then you would use this helper column instead of the created column in your COUNTIFS.

@SPark

@Paul Newcome yup, that did it! MUCH appreciated, you are a smartsheet ninja.

Paul Newcome

Happy to help! 🐱‍👤