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

Options

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!

Tags:

Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    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 - Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    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 - Sevan Technology

    Smartsheet Aligned Partner

  • BristolCVN
    BristolCVN ✭✭✭✭
    Options

    Absolutely Perfection! Thank you so much!

  • BristolCVN
    BristolCVN ✭✭✭✭
    Options

    @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
    BristolCVN ✭✭✭✭
    Options

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

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Options

    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!

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • BristolCVN
    BristolCVN ✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!