There are three main things to know about Dates and Times in Excel
1. Excel holds dates and times as a single decimal number, with the decimal part as the time from 0 to almost 1.
0.5 is 12 noon , i.e. half way through the day.
The integer or whole number part counts days since the start of 1900.
20 September 2009 is held as 40076 and 21 September 2009 will be 40077
2. What you see in a cell depends on how you format it. Formating such as "dd/mm/yyy hh:mm:ss AM/PM"
does not change the value stored.
3. When you enter data into a cell, Excel tests it for several things, including whether it looks like a date or time. If you enter 10:15 Excel will consider it a time and save it as 0.42708333 and format the cell as "h:mm". (Format may vary depending on default date and time settings - but you get the basic idea.
To get the number of days between dates just subtract one date from the other using only the integer part of the number.
I notice that you refer to conditional formatting again.
I think that you are trying to use conditional formatting for something it wasn't intended for.
Excel has a vast number of functions that can be used in cells and an equivalent number of commands in VBA. Conditional; formatting is really only to format cells, and not a way to perform calculations.
The date calculation above, using standard commands is simple.
If you want to highlight a certain value from the above calculation, such as an outlier result then a conditional format is useful.