Help

Calculate if record was modified after specific time of day

Topic Labels: Dates & Timezones
Solved
Jump to Solution
1510 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthew_Paterso
4 - Data Explorer
4 - Data Explorer

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?

1 Solution

Accepted Solutions
Simon_H
6 - Interface Innovator
6 - Interface Innovator

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

See Solution in Thread

2 Replies 2
Simon_H
6 - Interface Innovator
6 - Interface Innovator

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.