Time Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel spreadsheet with a field with the time format "7:00:00 AM".
When I import this spreadsheet, the value showing in the Import Spreadsheet
Wizard is .29166666667. Each time value has a different number similar to
this representing the cumulative percentage of the day gone by (i.e. 12:00 PM
has a value of .5). The problem is that when I try to use this field in a
query to match up two times, most of the times do not match. I am guessing
this is because the decimals are too long. The only times that match are the
smaller decimals such as 12:00 PM (.5) or 10:30 AM (.4375). Does anyone know
of a way around this? Any help is greatly appreciated.

Matt.
 
Matt

Define "match"...<g>

If you are saying that you only care about the first 4 decimal places, round
all your time values to four places and then compare.

Or am I missing something?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
You could do some messing around with the CDate and Format Functions.

Debug.Print CDate(.29166666667) = 7:00:00 AM
and
Debug.Print CDate(.291666) = 7:00:00 AM

However the following is False:
Debug.Print CDate(.291666) = CDate(.29166666667)

But is True as Format changes the times to a string:
Debug.Print Format(CDate(.291666),"HH:MM:SS") =
Format(CDate(.29166666667),"HH:MM:SS")

If you think rounding down to the next minute is OK to avoid a single second
from messing things up:
Debug.Print Format(CDate(.291666),"HH:MM") =
Format(CDate(.29166666667),"HH:MM")
 
I have an excel spreadsheet with a field with the time format "7:00:00 AM".
When I import this spreadsheet, the value showing in the Import Spreadsheet
Wizard is .29166666667. Each time value has a different number similar to
this representing the cumulative percentage of the day gone by (i.e. 12:00 PM
has a value of .5). The problem is that when I try to use this field in a
query to match up two times, most of the times do not match. I am guessing
this is because the decimals are too long. The only times that match are the
smaller decimals such as 12:00 PM (.5) or 10:30 AM (.4375).
Click to expand...

If this is temporal data then use temporal functionality (e.g.
DATEDIFF) at the appropriate level of granularity e.g.

SELECT CBOOL(DATEDIFF('S', CDATE(.291666), CDATE(.29166666667)) = 0)
AS are_equal_at_one_second_granularity

Jamie.

--
 
Back
Top