Using IF THEN to determine if the time stamp is within a range of time.

Options

I need to be able to determine if a transaction was completed on first shift or second shift. Is there a way to use an IF Then formula to return "1st shift" during transactions that happen between 5:00 am and 4:30 pm, and "2nd shift" for transaction time of 4:31pm to 4:59 am? Any help would be appreciated.

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @dminor764

    There is indeed. how ever if you are using the created date. Any formula's referencing it is actually in GMT. Thus I would recommend a couple of formulas and helper rows to achieve what you need.

    First I would create a helper column with the following formula. for the sake of this post I will call it Time

    =TIME(RIGHT(Created@row, 8), 1))

    Where created at row is what ever your created column is. What this does is pull the visible values from the column and changes them to the 24 hour clock. 16:31 for 4:31PM.

    Then you will be able to do this formula

    =IF(AND(Time@row >= "05:00", Time@row <= "16:30"),"First Shift","2nd Shift")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @dminor764

    There is indeed. how ever if you are using the created date. Any formula's referencing it is actually in GMT. Thus I would recommend a couple of formulas and helper rows to achieve what you need.

    First I would create a helper column with the following formula. for the sake of this post I will call it Time

    =TIME(RIGHT(Created@row, 8), 1))

    Where created at row is what ever your created column is. What this does is pull the visible values from the column and changes them to the 24 hour clock. 16:31 for 4:31PM.

    Then you will be able to do this formula

    =IF(AND(Time@row >= "05:00", Time@row <= "16:30"),"First Shift","2nd Shift")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • dminor764
    Options

    Perfect thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!