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?

=MOD(Staff[@[Clocked Out]],1)<$E$2

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


Do you want to excel in Excel?


Seeking Microsoft Certification?

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

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.