This is embarrassing from the complex questions I have seen. I am doing a query and need to be able to select a specific date. I am using the input [enter date]. Problem is the data is stored in dd/mm/yyy hh:mm:ss AM/PM format. Finally figured out that when I only input a date, it comes back with nothing. I need to have all records from 00:00:00 am to 23:59:59 for that date. How do I format the criteria. Thanks so much from a novice.

Hi, 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 400772. What you see in a cell depends on how you format it. Formating such as "dd/mm/yyy hh:mm:ss AM/PM"

doesnotchange 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 09With 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 A2I 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.

Regards

Hi Guys. A little more digging into the book and I found this. It really solved the problem.

Between [Please Enter Starting Date] And [Please Enter Ending Date]

Hi, I missed the point that your question was about Access not Excel

Regards

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History