Action based on System-Generated Modified Date/Time


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


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


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!