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)
In your case you need to enter dates/times in a way that Excel recognizes. Time is pretty easy - stick to 10:30 or 10:30:02 if you want seconds as well.
Dates entered in a format such as 20-Sep-09 work fine, and Excel considers this to be 2009.
To find all entries that have times within a certain day try something like this:
In A1 enter 20-Sep-09 00:00:00
In B1 enter 20-Sep-09 23:59:59
Format both as "dd-mmm-yyyy"
A1 & B1 will read 20-Sep-2009
Just to check it worked, in cell C1 put =B1-A1
Format C1 as a number with two decimal places
C1 will show 1.00
This means that there is one day difference between A1 and B1
Now you have the start and end value for 20 September 09
With those two values you can test any date and time entered to see if it is a time on 20 September
Any calculations of dates and times can use the standard + - = < > operators. Excel will do the calculation using the numbers it stored.
For example if you entered 20-Sep-09 12:00 in cell A2
enter this formula in cell B2
=IF(AND(A2>=$A$1,A2<=$B$1),"Its Today","Its Not today")
The result will be "Its Today"
Try changing the date/time in Cell A2
I hope this gives you enough information to solve your issue.
Remember that what you see in a cell depends on the formatting, and Excel may have something different stored in the cell - and this is particularly true for date and time.