Calculate if record was modified after specific time of day

Question

Hi, I want to write a formula that will return ‘Yes’ if a record has been modified before a specific time of day.
I have my team ticking a checkbox, which if checked before a certain time will mean a yes result. If the tick box is ticked after that time the result is no.
I’m using the ‘last modified time’ field type as the time stamp to use in the formula
I tried Is_Before with TimeSTR but not sure about how to specify the deadline time.
Any thoughts?

Simon_H · Accepted Answer

For checking the time you can use the hour and minute functions

HOUR(LAST_MODIFIED_TIME(Field)) < 16 //check if field was modified before 4pm

To check with minute accuracy you can combine HOUR and MINUTE with AND
AND( (HOUR(LAST_MODIFIED_TIME(Field)) < 16), (MINUTE(LAST_MODIFIED_TIME(Status)) < 31 ))

I’m not sure from your post how date factors in , but if it does you can add a check for that as the first parameter in the AND function

Keep in mind you may need to accommodate for GMT times

Thanks, that was the right way to approach it. I ended up using DATEADD to adjust for the timezone difference.

Matthew_Paterso · Answer

For checking the time you can use the hour and minute functions

HOUR(LAST_MODIFIED_TIME(Field)) < 16 //check if field was modified before 4pm

To check with minute accuracy you can combine HOUR and MINUTE with AND
AND( (HOUR(LAST_MODIFIED_TIME(Field)) < 16), (MINUTE(LAST_MODIFIED_TIME(Status)) < 31 ))

I’m not sure from your post how date factors in , but if it does you can add a check for that as the first parameter in the AND function

Keep in mind you may need to accommodate for GMT times

Thanks, that was the right way to approach it. I ended up using DATEADD to adjust for the timezone difference.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded