Ignore Number To Left of Decimal In Conditional Format

Hello, I am working on an attendance spreadsheet and would like to highlight when someone has been late by 15 minutes. The problem is that the values stored are stored in this format "dddd-mmmm-dd-yyyy h:mm AM/PM" so that gives me a decimal number of 43833.29167 for friday at 7:00 AM. 15 minutes after that comes to 43833.30208. Is there a way for conditional formatting to ignore all the number to the left of the decimal point, IE XXXXX.30208. Any help on this would be appreciated.

Thank You
-Max

=mod(43833.30208;1) 0,30208

Hello and welcome to the forum.

It would help for us to see a sample workbook that shows where the cells are for example.
Also, how do we know what time each person is supposed to come in?

The only advice that we can give without knowing this information is that =MOD(A1,1) where the full date/time is in A1 will return only the time.

If you require additional help, consider uploading a sample workbook.

It isn't accepting that equation. It says "Enter a valid formula"

May be ; should change to , . For my country separator is ;

Originally Posted by BMV

=mod(43833.30208;1)

Caveat: =MOD(DATE(2020,1,3)+TIME(9,15,0),1)>=TIME(9,15,0) returns FALSE(!), which is misleading if 9:15 is considered "late".

The point is: MOD(date+time,1) does not always result in the correct binary representation of "time" alone.

More reliable: --TEXT(date+time,"h:m")>=time

=(A2-$A$1)>=(15/1440) I would like see as =A2-$A$1-"00:15">=0 or =A2>=$A$1+"00:15" . The result is the same but it is more readable.

Originally Posted by joeu2004

The point is: MOD(date+time,1) does not always result in the correct binary representation of "time" alone.

it's true. Unfortunately floating point standard we cant change :-)

I have tried what you guys are telling me, but I am obviously doing something wrong, or my date format in the cells is whats messing everything up. I am trying to attach a sample of my workbook, but the site isn't letting me because i haven't posted enough. I will post a pic of my table, maybe that will help.

thanks for all the help so far. i will keep at it.

Capture.PNG

In your workbook from post #10, you want to highlight all of the times that are at least 15 minutes past the time in cell A1?

If the answer to post #11 is yes, try this:

Highlight C2:C45 > Conditional Formatting > New Rule > Use a formula
=MOD(C2,1)-TIME(0,15,0)>=A$1
Format: Fill color of your choice > OK > OK

Originally Posted by BMV

I would like see as =A2-$A$1-"00:15">=0 or =A2>=$A$1+"00:15" . The result is the same but it is more readable.

Mathematically, yes.

But again, not always in Excel for two possible reasons: (1) order sensitivity of binary arithmetic; and (2) tricks that Excel plays with comparison operators (">=") in this context.

Again, suppose A1 is 1/3/2020 9:00 and A2 is 1/3/2020 9:15. (It does not matter whether you interpret the date as m/d/yyyy or d/m/yyyy.)

=A2>=$A$1+"00:15" returns TRUE. But =A2-$A$1-"00:15">=0 returns FALSE(!).

In that example, the difference is due to the order sensitivity of binary arithmetic. A work-around is =A2-($A$1+"00:15")>=0.

But in theory, =A2>=$A$1+"00:15" might return a different result because when the left and right sides are non-zero, Excel rounds their binary values to 15 significant decimal digits for the purpose of the comparison.

However, in practice, that does not seem to be a problem for this date/time arithmetic.

Originally Posted by joeu2004

Mathematically, yes.
But again,

First part is relevant for Text value of A2. The second - It is regular problem and the reason is not 15 digits but IEEE 754. Different could be even in 8. There fore for EQ comparing is better to round both value or round result if Round(A2-$A$1-"00:15";x)>=0 or...

off
joeu2004 it's clear for me and probably will useful for others. In spit of I novice here I have experience but for me is not easy to write and explain because of language skill.

Thank you guys for helping me on this. 63falcondude, your formula kinda worked, but there were some times that were not getting highlighted when they should have been and others getting highlighted when they shouldn't have been, see pic below. protonLeah, your formula and setup did the trick, thank you!

Capture.PNG