Formula based on created time foiled by users in different time zones

Heather_Verde

I created a formula that looks at the created time and date and checks a box if the hour is after a certain time if the day is a certain date. When the box gets checked, it means the entry was made late, and an approval workflow kicks off to managers to review and approve the entry. The created time deadline is CST based. Little did I NOT realize that the created date time will adjust when the sheet is viewed by users in other time zones. This inadvertently triggered the approval workflow for multiple items.

I have currently turned off that workflow, but am wondering if anyone might have some suggestions on how to work around this or how I can make the time field be a static time zone.

Details: I have a helper column where the time is being pulled from the Created field. I have another formula that changes the hour to 24 hr. I have another helper that calculates the number of days between the created date and the due date. Because the due time is specific, I use both of these (the Networkdays calculation plus the time field to trigger the checkbox that would trigger the approval workflow. I'm open to any suggestions.

Paul Newcome

Try creating a copy of the sheet and having a copy/row automation trigger when the created date column is populated (make sure you have a unique ID on each row for this to work).

You would then use an INDEX/MATCH based on the unique ID to pull in the static data from the second sheet.