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

Long time no see! Everything is possible with formulas 😁. Use the update below. The formula works in a similar way, this time it converts the time to 24H, then if the time is greater than 1230 (that I have bolded in the formula below, so if the time changes again just update this value to the 24H equivalent) it will be next day. Anything on or before 1230 will be same day.

=IF((VALUE(SUBSTITUTE(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), ":", "")) + IF(CONTAINS("PM", RIGHT([Created Date]@row, 2)), 1200, 0)) > 1230, "Next Business Day", "Same 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.

BristolCVN

@Jason Tarpinian Hi Jason, quick follow up on this….a year later! Our expense team wants to move up the deadline trigger to 12:30pm from 1pm. How would I write that in the formula? I tried 12:30, but received Syntax Error. Or is it even possible to do this? Thanks!

Jason Tarpinian

Long time no see! Everything is possible with formulas 😁. Use the update below. The formula works in a similar way, this time it converts the time to 24H, then if the time is greater than 1230 (that I have bolded in the formula below, so if the time changes again just update this value to the 24H equivalent) it will be next day. Anything on or before 1230 will be same day.

=IF((VALUE(SUBSTITUTE(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), ":", "")) + IF(CONTAINS("PM", RIGHT([Created Date]@row, 2)), 1200, 0)) > 1230, "Next Business Day", "Same Day")

BristolCVN

@Jason Tarpinian Thank You Sir! This is great!