How can I display running time in elapsed hours and minutes?

[Al Ross, UK]

I need to be able to work with elapsed times for operation of a light
aircraft group. Flight times are logged in hours and minutes. I use Access
2000 and can work with the date/time data type just fine. But my problem is
this: when I try to display elapsed hours greater than 23h:59m the display
format rolls around as though it were giving a clock time. This is no use
for us. Things like next_maintenance-due are expressed in total running time
such as 3846h:20m which I want to display as 3846h:20m not "June 8, 1900
06:20"!

I guess I could hold the data as text strings and use conversion routines to
and from date/time for the math, but it seems needlessly tedious. Excel
allows me a display format that meets my needs, so I kind of expected Access
would too.

Any pointers / good ideas out there?

Thanks

Al

 

[Allen Browne]

The date/time field is not a suitable data type for storing durations.
Internally, the date/time field is a floating point number, where the
integer part represents the date, and the fractional part the time of day
(e.g. 6am = 0.25, noon = 0.5, ...).

Instead, use a Number field. Use a Long Integer, and store the value in
minutes, or use a Double and store the value in hours. If you store in
minutes, you can display as hours an minutes like this:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
If you store in hours and fractions of an hour, you can display hours and
minutes as:
=Int([Hours]) & Format(([Hours] - Int([Hours])) * 60, "\:00")
Either way the value will sum correctly, and be *far* more efficient than
text.

More info in this article:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For an interface, you can use two unbound text boxes for entering the hour
and minutes. Use the Current event of the form to populate them. Use their
AfterUpdate event to write the value to the hidden number field. Use the
Undo event of the form to populate them based on the OldValue of the hidden
field.

 

[Al Ross, UK]

Thanks for your comments. That's really helpful. I had briefly thought
about holding it all as minutes and then just got lazy. But your remark that
it will be more efficient encourages me. I guess I'd better get coding!

Thanks again,

Al

The date/time field is not a suitable data type for storing durations.
Internally, the date/time field is a floating point number, where the
integer part represents the date, and the fractional part the time of day
(e.g. 6am = 0.25, noon = 0.5, ...).

Instead, use a Number field. Use a Long Integer, and store the value in
minutes, or use a Double and store the value in hours. If you store in
minutes, you can display as hours an minutes like this:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
If you store in hours and fractions of an hour, you can display hours and
minutes as:
=Int([Hours]) & Format(([Hours] - Int([Hours])) * 60, "\:00")
Either way the value will sum correctly, and be *far* more efficient than
text.

More info in this article:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For an interface, you can use two unbound text boxes for entering the hour
and minutes. Use the Current event of the form to populate them. Use their
AfterUpdate event to write the value to the hidden number field. Use the
Undo event of the form to populate them based on the OldValue of the hidden
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to be able to work with elapsed times for operation of a light
aircraft group. Flight times are logged in hours and minutes. I use
Access
2000 and can work with the date/time data type just fine. But my problem
is
this: when I try to display elapsed hours greater than 23h:59m the display
format rolls around as though it were giving a clock time. This is no use
for us. Things like next_maintenance-due are expressed in total running
time
such as 3846h:20m which I want to display as 3846h:20m not "June 8, 1900
06:20"!

I guess I could hold the data as text strings and use conversion routines
to
and from date/time for the math, but it seems needlessly tedious. Excel
allows me a display format that meets my needs, so I kind of expected
Access
would too.

Any pointers / good ideas out there?

 

[pauld]

I would like to calculate elapsed time in hours, minutes, and seconds between
the start of a race and the end of a race. The elapsed time will range from
10-36 hours. Any idea how to calculate this elapsed time ? The format of the
times MUST be in hours, minutes, and seconds. Fields used are "Start Time",
"Finish Time" and "Elpased Time"

As noted elsewhere this is easily done in Excel.....but I have to use Access.

Thanks for anyone's help

The date/time field is not a suitable data type for storing durations.
Internally, the date/time field is a floating point number, where the
integer part represents the date, and the fractional part the time of day
(e.g. 6am = 0.25, noon = 0.5, ...).

Instead, use a Number field. Use a Long Integer, and store the value in
minutes, or use a Double and store the value in hours. If you store in
minutes, you can display as hours an minutes like this:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
If you store in hours and fractions of an hour, you can display hours and
minutes as:
=Int([Hours]) & Format(([Hours] - Int([Hours])) * 60, "\:00")
Either way the value will sum correctly, and be *far* more efficient than
text.

More info in this article:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For an interface, you can use two unbound text boxes for entering the hour
and minutes. Use the Current event of the form to populate them. Use their
AfterUpdate event to write the value to the hidden number field. Use the
Undo event of the form to populate them based on the OldValue of the hidden
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to be able to work with elapsed times for operation of a light
aircraft group. Flight times are logged in hours and minutes. I use
Access
2000 and can work with the date/time data type just fine. But my problem
is
this: when I try to display elapsed hours greater than 23h:59m the display
format rolls around as though it were giving a clock time. This is no use
for us. Things like next_maintenance-due are expressed in total running
time
such as 3846h:20m which I want to display as 3846h:20m not "June 8, 1900
06:20"!

I guess I could hold the data as text strings and use conversion routines
to
and from date/time for the math, but it seems needlessly tedious. Excel
allows me a display format that meets my needs, so I kind of expected
Access
would too.

Any pointers / good ideas out there?

 

[Douglas J. Steele]

Just to add to what I said in another thread (you're asking this in far too
many places!), don't confuse format with storage. Just because you need to
display the duration in hh:mm:ss does not mean you need to store it that
way.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I would like to calculate elapsed time in hours, minutes, and seconds
between
the start of a race and the end of a race. The elapsed time will range
from
10-36 hours. Any idea how to calculate this elapsed time ? The format of
the
times MUST be in hours, minutes, and seconds. Fields used are "Start
Time",
"Finish Time" and "Elpased Time"

As noted elsewhere this is easily done in Excel.....but I have to use
Access.

Thanks for anyone's help

The date/time field is not a suitable data type for storing durations.
Internally, the date/time field is a floating point number, where the
integer part represents the date, and the fractional part the time of day
(e.g. 6am = 0.25, noon = 0.5, ...).

Instead, use a Number field. Use a Long Integer, and store the value in
minutes, or use a Double and store the value in hours. If you store in
minutes, you can display as hours an minutes like this:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
If you store in hours and fractions of an hour, you can display hours and
minutes as:
=Int([Hours]) & Format(([Hours] - Int([Hours])) * 60, "\:00")
Either way the value will sum correctly, and be *far* more efficient than
text.

More info in this article:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For an interface, you can use two unbound text boxes for entering the
hour
and minutes. Use the Current event of the form to populate them. Use
their
AfterUpdate event to write the value to the hidden number field. Use the
Undo event of the form to populate them based on the OldValue of the
hidden
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to be able to work with elapsed times for operation of a light
aircraft group. Flight times are logged in hours and minutes. I use
Access
2000 and can work with the date/time data type just fine. But my
problem
is
this: when I try to display elapsed hours greater than 23h:59m the
display
format rolls around as though it were giving a clock time. This is no
use
for us. Things like next_maintenance-due are expressed in total
running
time
such as 3846h:20m which I want to display as 3846h:20m not "June 8,
1900
06:20"!

I guess I could hold the data as text strings and use conversion
routines
to
and from date/time for the math, but it seems needlessly tedious.
Excel
allows me a display format that meets my needs, so I kind of expected
Access
would too.

Any pointers / good ideas out there?

 

[Al Ross, UK]

So if I get the gist of the answers here it is basically this: what PaulD
wants to do, and what I wanted to do a couple of years back is not available
in Access.

Frankly it surprises me. MS Excel seems perfectly capable of having an
output format that outputs times formatted tha way the Paul & I had hoped to
find. So why not Access? Allegedly its not sensible because of the storage
format for time which will lose accuracy.

I don't actually buy that. Time is reportedly stored in a 64-bit floating
point fomat. To store time accurate to one second using an integer demands
the trifling sum of 25 bits of storage. If time is to be resolved to one
second over a millenium we still fit neatly inside 35 bits - leaving an
incredible 28 bits for sign and exponent elements of a floating point number
and a great deal more than is actually allocated to sign and exponent.

What this all means is that it is perfectly practical to do arithmentic on
elapsed times of over 1,000 years and still produce answers correct to one
second.

So, back to the plot: it may be considered impure by some, but really all I
want is to be able to do some arithmetic on times and display the ensuing
answer in hours minutes and perhaps seconds. I can do this very
satisfactorily in Excel using date / time formatted data - both calculation
and display.

So why can't we get a similar display format in Access? If it's possible in
Excel then it must be possible in Access.

What we seem to have is a defensive attiude that tells us we should look at
it another way. Instead I'd rather like MS to listen to customers and
deliver instead of telling us we can't do what can quite obviously be done by
other MS programmers.

Sure I can get on with programming it another way - but why should I need to
do a knife and fork programming job on such an obvious and simple requirement?

Or don't the Excel and Access teams talk to one another?

Al

Just to add to what I said in another thread (you're asking this in far too
many places!), don't confuse format with storage. Just because you need to
display the duration in hh:mm:ss does not mean you need to store it that
way.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I would like to calculate elapsed time in hours, minutes, and seconds
between
the start of a race and the end of a race. The elapsed time will range
from
10-36 hours. Any idea how to calculate this elapsed time ? The format of
the
times MUST be in hours, minutes, and seconds. Fields used are "Start
Time",
"Finish Time" and "Elpased Time"

As noted elsewhere this is easily done in Excel.....but I have to use
Access.

Thanks for anyone's help

The date/time field is not a suitable data type for storing durations.
Internally, the date/time field is a floating point number, where the
integer part represents the date, and the fractional part the time of day
(e.g. 6am = 0.25, noon = 0.5, ...).

Instead, use a Number field. Use a Long Integer, and store the value in
minutes, or use a Double and store the value in hours. If you store in
minutes, you can display as hours an minutes like this:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
If you store in hours and fractions of an hour, you can display hours and
minutes as:
=Int([Hours]) & Format(([Hours] - Int([Hours])) * 60, "\:00")
Either way the value will sum correctly, and be *far* more efficient than
text.

More info in this article:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For an interface, you can use two unbound text boxes for entering the
hour
and minutes. Use the Current event of the form to populate them. Use
their
AfterUpdate event to write the value to the hidden number field. Use the
Undo event of the form to populate them based on the OldValue of the
hidden
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to be able to work with elapsed times for operation of a light
aircraft group. Flight times are logged in hours and minutes. I use
Access
2000 and can work with the date/time data type just fine. But my
problem
is
this: when I try to display elapsed hours greater than 23h:59m the
display
format rolls around as though it were giving a clock time. This is no
use
for us. Things like next_maintenance-due are expressed in total
running
time
such as 3846h:20m which I want to display as 3846h:20m not "June 8,
1900
06:20"!

I guess I could hold the data as text strings and use conversion
routines
to
and from date/time for the math, but it seems needlessly tedious.
Excel
allows me a display format that meets my needs, so I kind of expected
Access
would too.

Any pointers / good ideas out there?

 

[Douglas J. Steele]

It's not a case of "the storage format for time which will lose accuracy".
It's a case that the Date data type is only intended to hold a timestamp.

It's not unreasonable to ask Microsoft to also have a duration data type.
They just haven't added one yet.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So if I get the gist of the answers here it is basically this: what PaulD
wants to do, and what I wanted to do a couple of years back is not
available
in Access.

Frankly it surprises me. MS Excel seems perfectly capable of having an
output format that outputs times formatted tha way the Paul & I had hoped
to
find. So why not Access? Allegedly its not sensible because of the
storage
format for time which will lose accuracy.

I don't actually buy that. Time is reportedly stored in a 64-bit floating
point fomat. To store time accurate to one second using an integer
demands
the trifling sum of 25 bits of storage. If time is to be resolved to one
second over a millenium we still fit neatly inside 35 bits - leaving an
incredible 28 bits for sign and exponent elements of a floating point
number
and a great deal more than is actually allocated to sign and exponent.

What this all means is that it is perfectly practical to do arithmentic on
elapsed times of over 1,000 years and still produce answers correct to one
second.

So, back to the plot: it may be considered impure by some, but really all
I
want is to be able to do some arithmetic on times and display the ensuing
answer in hours minutes and perhaps seconds. I can do this very
satisfactorily in Excel using date / time formatted data - both
calculation
and display.

So why can't we get a similar display format in Access? If it's possible
in
Excel then it must be possible in Access.

What we seem to have is a defensive attiude that tells us we should look
at
it another way. Instead I'd rather like MS to listen to customers and
deliver instead of telling us we can't do what can quite obviously be done
by
other MS programmers.

Sure I can get on with programming it another way - but why should I need
to
do a knife and fork programming job on such an obvious and simple
requirement?

Or don't the Excel and Access teams talk to one another?

Al

Just to add to what I said in another thread (you're asking this in far
too
many places!), don't confuse format with storage. Just because you need
to
display the duration in hh:mm:ss does not mean you need to store it that
way.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I would like to calculate elapsed time in hours, minutes, and seconds
between
the start of a race and the end of a race. The elapsed time will range
from
10-36 hours. Any idea how to calculate this elapsed time ? The format
of
the
times MUST be in hours, minutes, and seconds. Fields used are "Start
Time",
"Finish Time" and "Elpased Time"

As noted elsewhere this is easily done in Excel.....but I have to use
Access.

Thanks for anyone's help

:

The date/time field is not a suitable data type for storing durations.
Internally, the date/time field is a floating point number, where the
integer part represents the date, and the fractional part the time of
day
(e.g. 6am = 0.25, noon = 0.5, ...).

Instead, use a Number field. Use a Long Integer, and store the value
in
minutes, or use a Double and store the value in hours. If you store in
minutes, you can display as hours an minutes like this:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
If you store in hours and fractions of an hour, you can display hours
and
minutes as:
=Int([Hours]) & Format(([Hours] - Int([Hours])) * 60, "\:00")
Either way the value will sum correctly, and be *far* more efficient
than
text.

More info in this article:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For an interface, you can use two unbound text boxes for entering the
hour
and minutes. Use the Current event of the form to populate them. Use
their
AfterUpdate event to write the value to the hidden number field. Use
the
Undo event of the form to populate them based on the OldValue of the
hidden
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to be able to work with elapsed times for operation of a light
aircraft group. Flight times are logged in hours and minutes. I
use
Access
2000 and can work with the date/time data type just fine. But my
problem
is
this: when I try to display elapsed hours greater than 23h:59m the
display
format rolls around as though it were giving a clock time. This is
no
use
for us. Things like next_maintenance-due are expressed in total
running
time
such as 3846h:20m which I want to display as 3846h:20m not "June 8,
1900
06:20"!

I guess I could hold the data as text strings and use conversion
routines
to
and from date/time for the math, but it seems needlessly tedious.
Excel
allows me a display format that meets my needs, so I kind of
expected
Access
would too.

Any pointers / good ideas out there?