Re: Time Format in Excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Access date/time fields store points in time encoded as real numbers.
The integer part of the number is the number of days (day 1 was 31
December 1899, the reason why is too embarrasing to recount); the
fractional part represents time of day (0.25 = 6 am; 0.5 = 12 noon and
so on).

If you put "6:00" in a date field, what gets stored is 0.25 - i.e. 06:00
on day 0, 30 December 1899. Most of the time, using ordinary addition
and subtraction on date/time values works OK, e.g. if
EndTime = 12:00 (0.5)
SetupTime = 6:00 (0.25)
Duration = EndTime - SetupTime
0.5 - 0.25 = 0.25
then CTime(0.25) gives the expected result of 06:00 (or the unexpected
result of 6:00 am, depending on the date/time formats you're using!).
There can be other snags, so in general it's safer to use the date/time
functions provided:

DateDiff() returns the difference between two date/time values in years,
days, hours, minutes or other units. That's what you need to use for the
present problem.

DateAdd() adds or subtracts a given number of days, hours (etc.) to or
from a date/time value.





On Wed, 31 May 2006 08:49:02 -0700, Beyuduzz
<Beyuduzz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I have a query that pulls the information below. I export it to excel to
create a graph, but the time fields are giving me problems. The duration
colum in access is formated at SHORT TIME. If it isn't, I get a decimal
number that doesn't make sense (see below). The other times are irrelevent
to the graph. I only care about the Room and duration. When imported into
excel, I want to Sum the duration column into a total number of hours.

MeetingDate | Room | SetupTime | StartTime | EndTime | Duration{hhmm}
5/19/2006 MDA 6:00 6:30 12:00 0.25

Here is what I have done so far to the duration field.
-- [endtime]-[setuptime]*60 this returns a medium time format
-- [endtime]-[setuptime]/60 this returns a medium time format
-- [endtime]-[setuptime]/24 this returns a medium time format
-- [endtime]-[setuptime]*24 this returns a crazy long date format

I want the duration column in a proper format that will SUM correctly when
exported to excel. Any help please.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Relevant Pages

  • Re: Time Format in Excel
    ... the reason why is too embarrasing to recount); ... Duration = EndTime - SetupTime ... depending on the date/time formats you're using!). ...
    (microsoft.public.access.externaldata)
  • Re: Storing time as integers
    ... duration, it can be used for the latter provided that each duration is less ... If you do maths on date/time values you get some strange results if you ... If you need to store durations of more than 24 hours you have little choice ... You'll have noticed that the function includes an optional blnShowdays ...
    (microsoft.public.access.queries)
  • Re: String to Time
    ... then you can add two Date/Time ... use an update query to save the converted values: ... Note that using a date/time field to hold a duration value ... format called General Number, but it does not apply to text ...
    (microsoft.public.access.formscoding)
  • Re: Time Format in Excel
    ... development of the first version of Lotus 1-2-3. ... same as in Excel. ... Duration = EndTime - SetupTime ... depending on the date/time formats you're using!). ...
    (microsoft.public.access.externaldata)
  • Re: Storing time as integers
    ... duration, it can be used for the latter provided that each duration is less ... If you do maths on date/time values you get some strange results if you ... If you need to store durations of more than 24 hours you have little choice ... you can quite happily use a date/time data type. ...
    (microsoft.public.access.queries)