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."
Any help out there?
Thank you.
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."
Any help out there?
Thank you.