Forum Discussion
Error in Comparing Date/Time Columns
I am trying to define the shift of a person using his In Time data. But the measure classifies a few time instants incorrectly.
The error is only with work shift C.
A, B and Undefined are correct but some instances of them are getting classified as C incorrectly.
The measure for WorkShift is:
WorkShift =
IF(
(Sheet1[In Time] > TIME(7,0,0) ) && (Sheet1[In Time] < TIME(9,0,0) ) , "A" ,
IF ( (Sheet1[In Time] < TIME(17,0,0) ) && (Sheet1[In Time] > TIME(15,0,0) ) , "B" ,
IF( (Sheet1[In Time] < TIME(1,0,0) ) || (Sheet1[In Time] > TIME(23,0,0) ) ,"C" ,"UNDEFINED")
)
)The pic shows the error (wrong classification):
https://drive.google.com/file/d/1Tvu3IVCENsI8bm3TrEknerIGIt465oPh/view?usp=sharing
Yes, your 'InTime' column is currently time format -> 10:01:01
When you use the TIME() function you will return datetime format -> 1899-01-01 10:01:01If you create a new column, converting the values that you have in your 'InTime' column using TIME() you should get the same timestamps but in datetime and with 1899-01-01 as the Y/M/D
Use that column instead of the 'InTime' column and the original expression should work.
