• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a formula to convert a time stamp into work shifts

dgoscinski

New Member
I have an issue where I am trying to convert a timestamp 9/23/2019 22:46:36 to convert to which shift was worked.

Here is the scenario: I have 2 employees that use an app for work purposes. This app records the timestamp. They use the same tablet. So when I get data from the app I get the timestamp in one column and username in another. But it would be helpful to create a formula that reads the time stamp in a2 and spit out a result that says "Day Shift" or "Night Shift." Day shift runs from 6:30AM to 5:00PM. Night shift runs from 5:00PM to 3:30AM.
I am currently using this formula
=IF(AND(A2>=$V$2,A2<=$V$3),$W$2,$W$4)
A2 is the time stamp
V2 is 6:30:00
V3 is 17:00:00
V4 is 3:30:00 (thought it is not referenced in the above formula. Why include it? If the first timestamp is between the hours of 6:30am and 17:00, it is dayshift, otherwise it's night shift.)
W2 says Day Shift
W4 says Night Shift

All results from above formula return "Night Shift."63984

Any help out there?

Thank you.
 

Attachments

  • 1573873020255.png
    1573873020255.png
    240.8 KB · Views: 20
Hi ,

Are you sure your recalculation is on Automatic ?

Or have your times been entered as Text ?

I tried your formula on the time 06:39:31 , and it correctly gives Day Shift.

Narayan
 
try this tweak:
Code:
=IF(AND(MOD(A2,1)>=$V$2,MOD(A2,1)<=$V$3),$W$2,$W$4)
or without reference to other cells:
Code:
=INDEX({"night";"day";"night"},MATCH(MOD(A2,1),{0;0.270833333333333;0.708333333333333}))
 
try this tweak:
Code:
=IF(AND(MOD(A2,1)>=$V$2,MOD(A2,1)<=$V$3),$W$2,$W$4)
or without reference to other cells:
Code:
=INDEX({"night";"day";"night"},MATCH(MOD(A2,1),{0;0.270833333333333;0.708333333333333}))
Thank you. The INDEX works perfectly. Still unsure why mine isn't working properly. Oh well. The INDEX works great. Thanks a million.
 
Still unsure why mine isn't working properly.
Hi ,

I missed reading this in your original post :

9/23/2019 22:46:36

This is not a time stamp ; it is a date and time stamp.

Dates are integers , which go from 1 upwards , with today 17 November 2019 being the integer 43786.

Time values are a decimal value between 0 , which is midnight , and 1 which is also midnight.

Thus a genuine timestamp will be only a decimal between 0 and .999988 (23:59:59)

Thus when you compare a date and time stamp given above , what you are comparing is a decimal value 43731.9490277778 with another decimal value equivalent to 0.270833333333333 (06:30:00) and 0.708333333333333 (17:00:00).

Thus , when you use a formula such as =MOD(43731.9490277778 , 1) , what you are doing is removing the integer value of 43731 , which leaves you with only the decimal value of 0.9490277778 ; now comparing this to 06:30:00 and 17:00:00 will return the correct result.

Narayan
 
Back
Top