Formula Needed to Check Time From Created Date and Put Into 2 Statuses

BristolCVN

Hi All,

Trying to work with Created Date column to extract the time from Time Stamp, then look at the time to determine if it falls within two statuses. This is for a Rush Payment Cutoff Time. If an entry is submitted in our sheet before 1pm that day, I want the column to say "Same Day" and if after 1pm, have it say "Next Business Day". I have added a column after Created Date and using =Right([Created Date]@row, 8) I am able to extract the time. Would I need to convert that to military time in order to try an achieve what I am looking for?

Thanks in advance!

Jason Tarpinian

Here's an option for you to use. It first checks AM/PM, if it is not AM, then it makes sure that the hour is =12. No need to translate to military time.

=IF(OR(CONTAINS("AM", RIGHT([Created Date]@row, LEN([Created Date]@row) - FIND(" ", [Created Date]@row))), VALUE(LEFT(RIGHT([Created Date]@row, LEN([Created Date]@row) - FIND(" ", [Created Date]@row)), FIND(":", RIGHT([Created Date]@row, LEN([Created Date]@row) - FIND(" ", [Created Date]@row))) - 1)) = 12), "Same Day", "Next Business Day")

Jason Tarpinian

Here's an option for you to use. It first checks AM/PM, if it is not AM, then it makes sure that the hour is =12. No need to translate to military time.

=IF(OR(CONTAINS("AM", RIGHT([Created Date]@row, LEN([Created Date]@row) - FIND(" ", [Created Date]@row))), VALUE(LEFT(RIGHT([Created Date]@row, LEN([Created Date]@row) - FIND(" ", [Created Date]@row)), FIND(":", RIGHT([Created Date]@row, LEN([Created Date]@row) - FIND(" ", [Created Date]@row))) - 1)) = 12), "Same Day", "Next Business Day")

BristolCVN

Absolutely Perfection! Thank you so much!

BristolCVN

@Jason Tarpinian Question for you, I'm noticing that the Time Column (Rush Payment Cutoff Time), which is based on the Created Date column, is changing to 3 hours earlier. I have the column locked and as a Column formula so that no one can change the times. Some of the time changes are changing the statuses to "Same Day" when they shouldn't be. Any idea why the times might be changing?

Thanks!

BristolCVN

@Jason Tarpinian and strangely, they just switched back. I'm stumped why they changed and reverted back.

Jason Tarpinian

I'm not sure what would've caused that, were you traveling across time-zones that might've mixed up Smartsheet's tracker? I haven't seen that one before, just keep a close eye on it I guess and track if you do anything particular that triggers it!

BristolCVN

@Jason Tarpinian Lol, nope in one place the whole time. I'll keep an eye on it and let you know if it persists.