Time Format in Excel

[Guest]

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.

 

[Guest]

I FOUND IT!!!

Instead of using a time format in the properties, I typed this below into
the field of the query. the "n" produces the time difference in minutes, I
then divide that by 60, giving me hours.minutes. MAN, I'm so excited I
figured it out on my own!!!!

Duration: DateDiff("n",[Setuptime],[EndTime])/60

 

[Guest]

The first problem you have is trying to think of the difference between two
times as a time. It is not a time, it is a duration in some interval. In
this case, it appears you are looking for hours and minutes. You are getting
the decimal because you math is incorrect for dealing with times and
duration. Your duration field should be an Integer data type. To get the
duration in minutes, you would use:

[duration] = DateDiff("n", [SetupTime], [StartTime])
Which, for the example you provided will return a value of 30, which is the
number of minutes between the two times. Now, if the StartTime were 7:30,
the difference would be 150 minutes, which would be 1 hour and 30 minutes.
Doesn't matter, that is the way it should be carried. To get the format you
want to go to excel or present to users, you can use the format function to
present it as hours and minutes.

Format([duration] \ 60 & [duration] Mod 60, "00:00")
for 30 minutes it will return
00:30
for 150 minutes, it will return
01:30

 

[John Nurick]

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.

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.

 

[Guest]

So, there is a reason December 31, 1899 was chosen as Day 1. This has always
been a mystery to me, so even if you consider it embarrasing, I promise to
neither laugh nor make rude comments if you would be so kind as to share this
knowledge.

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.

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.

 

[Guest]

Yeah, I'm curious too.
--
I''m a novice with an advance way of thinking.

So, there is a reason December 31, 1899 was chosen as Day 1. This has always
been a mystery to me, so even if you consider it embarrasing, I promise to
neither laugh nor make rude comments if you would be so kind as to share this
knowledge.

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.

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.

 

[Guest]

So did I do it wrong by using

DurationateDiff("n", [setuptime], [endtime])/60


--
I''m a novice with an advance way of thinking.

The first problem you have is trying to think of the difference between two
times as a time. It is not a time, it is a duration in some interval. In
this case, it appears you are looking for hours and minutes. You are getting
the decimal because you math is incorrect for dealing with times and
duration. Your duration field should be an Integer data type. To get the
duration in minutes, you would use:

[duration] = DateDiff("n", [SetupTime], [StartTime])
Which, for the example you provided will return a value of 30, which is the
number of minutes between the two times. Now, if the StartTime were 7:30,
the difference would be 150 minutes, which would be 1 hour and 30 minutes.
Doesn't matter, that is the way it should be carried. To get the format you
want to go to excel or present to users, you can use the format function to
present it as hours and minutes.

Format([duration] \ 60 & [duration] Mod 60, "00:00")
for 30 minutes it will return
00:30
for 150 minutes, it will return
01:30

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]

Actually, 1 January 1900 was chosen as Day 1. IIRC this was during the
development of the first version of Lotus 1-2-3. But depending on which
version you believe, either the Lotus people thought 1900 was a leap
year, or they couldn't be bothered to code their date functions to
handle the Gregorian calendar.

To make it easier to read and write Lotus workbooks (essential for
sales), the first Windows version of Excel copied the mistake: leap and
non-leap years were handled correctly - with the deliberate exception of
1900.

Subsequently 31 December 1899 was chosen as Day 1 in VBA and Access,
because that meant date values from 1 March 1900 onwards would be the
same as in Excel.

The old system survives in Excel's worksheet functions. Try this in the
Excel VBE's Immediate Pane:

?Application.WorksheetFunction.Text(38868, "dd mmmm yyyy")
31 May 2006
?Format(38868,"dd mmm yyyy")
31 May 2006

?Application.WorksheetFunction.Text(1, "dd mmmm yyyy")
01 January 1900
?Format(1, "dd mmm yyyy")
31 Dec 1899

So, there is a reason December 31, 1899 was chosen as Day 1. This has always
been a mystery to me, so even if you consider it embarrasing, I promise to
neither laugh nor make rude comments if you would be so kind as to share this
knowledge.

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.

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.