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.6875``

PS: 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``

## Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!