The first step to conditional formatting a date or time in a DateTime is understanding how Excel stores dates and times.
Date is stored as an integer with 1/1/1900 = 1 therefore 9/2/2022 is stored as ► 44806
Time is stored as a fraction of 24 hours therefore 4:30:00 PM is stored as ► 0.6875 which is (16.5/24)
A DateTime is stored as a summation of both date + time 9/2/2022 4:30:00 PM is stored as ► 44806.6875
How do you highlight the staff who left early in the below data?
The only trick here is to know that the MOD function with 1 as the divisor will always return the decimal part. And Since Excel stores time as a fraction of 24, it will always return the time part.
= MOD(44806.6875, 1) =0.6875PS: For effective Conditional formatting, always test your formulas first on the worksheet as shown in the video.
How do you highlight the staff who left early on 4/2/2019 ONLY in the below data?
The only trick here is to realize that excel stores time as an integer, therefore to extract date using the INT function
= INT(44806.6875) =44806 ► 9/2/2022
Based on the question on the employees that left work on 4/2/2019 before 4.30pm, the results on the T/F column on time gives a false for Rick Reed – 4/2/2019 4:26:15pm, why so?
Also, the conditional formatting highlights those false n true for the time where date is T
Apologies Jacky, I had the official check-out time on relative reference when showing the results in the column but changed it to absolute in the conditional formatting rule.
I have changed the video.