Time format for elapsed time

[SilverSwimmer]

How can I get Access to recognize a time field as elapsed time, not time of
day? I use this for race results, and the format is usually: h:mm:ss. When
I import from Excel, I am importing a field formatted as: [hh]:mm, even
though the [hh] is really minutes. When I do this, it converts any times of
24 minutes and above, e.g., 24:02 becomes 0:02. Basically, I want to import
into Access exactly what it sees in Excel without regard to the formatting.
I have tried everything and am out of solutions. Anyone out there? Thanks!

 

[SilverSwimmer]

Thank you. I think I understand. But, my problem now is that all of my data
in the Excel files is formatted as time and reads, e.g., 24:02. Ideally, I
would like to separate it into two fields before importing into Access. But
when I try to convert "Text to Columns" it reads as a date/time. How can I
create the two colums and get the data as it reads, i.e., 24 in one column,
then 02 in the next?

Thanks for your patience!

Hello SilverSwimmer,

Date/Time format is just what it says; with this format you record dates and
time. Elapsed time is neither a date or a time. If you import 24:02; that's
text! You then need to parse it into two fields; 24 minutes and 02 seconds.
The minutes and seconds fields can be text or better can be number. If there
will never be fractions of minute, the minute field can be Integer. The
same with the seconds field. If the value can be a fraction of a minute, the
field must be Single. The same with the seconds field.

You can parse it using the Instr function to find the colon.

Steve
(e-mail address removed)

How can I get Access to recognize a time field as elapsed time, not time
of
day? I use this for race results, and the format is usually: h:mm:ss.
When
I import from Excel, I am importing a field formatted as: [hh]:mm, even
though the [hh] is really minutes. When I do this, it converts any times
of
24 minutes and above, e.g., 24:02 becomes 0:02. Basically, I want to
import
into Access exactly what it sees in Excel without regard to the
formatting.
I have tried everything and am out of solutions. Anyone out there?
Thanks!

 

[Frank H]

Taking times from Excel to access can be confusing, but it actually goes very
smoothly. YOu may be aware that both Access and Excel utilize time as
decimals, where the whole number part is the date (number of days since
1/1/1900) and the decimal part is the time (.5 = 12 noon, .75 = 6 pm).

In Excel this is fairly easy to see (just take any format of date or time,
and hit the "," comma format: you will see the decimal representation of that
time)

Access seems to take great pains to "protect" you from seeing this.

However, if you take your Excel data formatted as hh:mm:ss,
import it to an Access table as date/time (it will look wrong at first),
in the table design view, change the format to "hh:nn:ss" ("n" is minute,
"m" is month),
You will see your elapasd times just as you saw them in Excel.

The lines below are from the access table after doing the above.

The NumericTime I got in Excel by copying just the value from the cell to
the left, formatting it with the comma format button, and imported it to
Access as a double.

Person MyRunTime NumericTime
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Jack 1:15:00 .055020833333333333

 

[SilverSwimmer]

Thank you - this has been helpful/educational. I still have a
dilemma,though. I am importing the race results from our website (hundreds
of files from 1975-present that I am converting to an Access database). When
I follow your instructions, Excel converts a time of 18:02 (18 minutes, two
seconds) to 18:02:00 and, for any times 24 minutes or greater, e.g., 24:32,
it converts as 0:32:00.

The only solution I have come up with so far is to import from the web into
a text editor, then import to Access as text. This seems to work, but if you
can think of a better solution, I would love to hear it!

Thank you.
Peg
Tallahassee, FL

Taking times from Excel to access can be confusing, but it actually goes very
smoothly. YOu may be aware that both Access and Excel utilize time as
decimals, where the whole number part is the date (number of days since
1/1/1900) and the decimal part is the time (.5 = 12 noon, .75 = 6 pm).

In Excel this is fairly easy to see (just take any format of date or time,
and hit the "," comma format: you will see the decimal representation of that
time)

Access seems to take great pains to "protect" you from seeing this.

However, if you take your Excel data formatted as hh:mm:ss,
import it to an Access table as date/time (it will look wrong at first),
in the table design view, change the format to "hh:nn:ss" ("n" is minute,
"m" is month),
You will see your elapasd times just as you saw them in Excel.

The lines below are from the access table after doing the above.

The NumericTime I got in Excel by copying just the value from the cell to
the left, formatting it with the comma format button, and imported it to
Access as a double.

Person MyRunTime NumericTime
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Jack 1:15:00 .055020833333333333
--
Frank H
Rockford, IL

How can I get Access to recognize a time field as elapsed time, not time of
day? I use this for race results, and the format is usually: h:mm:ss. When
I import from Excel, I am importing a field formatted as: [hh]:mm, even
though the [hh] is really minutes. When I do this, it converts any times of
24 minutes and above, e.g., 24:02 becomes 0:02. Basically, I want to import
into Access exactly what it sees in Excel without regard to the formatting.
I have tried everything and am out of solutions. Anyone out there? Thanks!

 

[JBW]

If you import your elapsed times as decimals as shown by Frank H. you can
then create a query and module using the following coding to get the elapsed
time as text. If you need the elapsed time to be time in minutes/secs, this
won't help. I am trying to figure out how to do the latter so I can get an
average elapsed time.

Anyway, here is the coding for your query:

SELECT Temp.TAGID, Temp.TimeBetween, TimeElapsed([TimeBetween]) AS
Time_Interval INTO tblTimeIntervals
FROM Temp;


Temp is what I named my table that contained time as decimals. TagID was
the field for an individual and TimeBetween was the field that contained
decimal time.

Here is the coding for the TimeElapsed module:
Public Function TimeElapsed(dblTotalTime As Double, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngDays As Long
Dim lngHours As Long
Dim strMinutesSeconds As String

' get number of days
lngDays = Int(dblTotalTime)

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")
' return total elapsed time either as total hours etc
' or as days:hours etc
If blnShowDays Then
lngHours = lngHours - (lngDays * HOURSINDAY)
TimeElapsed = lngDays
Else
TimeElapsed = Format(lngHours, "00") & strMinutesSeconds
End If

End Function

Thank you - this has been helpful/educational. I still have a
dilemma,though. I am importing the race results from our website (hundreds
of files from 1975-present that I am converting to an Access database). When
I follow your instructions, Excel converts a time of 18:02 (18 minutes, two
seconds) to 18:02:00 and, for any times 24 minutes or greater, e.g., 24:32,
it converts as 0:32:00.

The only solution I have come up with so far is to import from the web into
a text editor, then import to Access as text. This seems to work, but if you
can think of a better solution, I would love to hear it!

Thank you.
Peg
Tallahassee, FL

Taking times from Excel to access can be confusing, but it actually goes very
smoothly. YOu may be aware that both Access and Excel utilize time as
decimals, where the whole number part is the date (number of days since
1/1/1900) and the decimal part is the time (.5 = 12 noon, .75 = 6 pm).

In Excel this is fairly easy to see (just take any format of date or time,
and hit the "," comma format: you will see the decimal representation of that
time)

Access seems to take great pains to "protect" you from seeing this.

However, if you take your Excel data formatted as hh:mm:ss,
import it to an Access table as date/time (it will look wrong at first),
in the table design view, change the format to "hh:nn:ss" ("n" is minute,
"m" is month),
You will see your elapasd times just as you saw them in Excel.

The lines below are from the access table after doing the above.

The NumericTime I got in Excel by copying just the value from the cell to
the left, formatting it with the comma format button, and imported it to
Access as a double.

Person MyRunTime NumericTime
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Joe 0:15:00 .0104166666666667
Jack 1:15:00 .055020833333333333
--
Frank H
Rockford, IL

How can I get Access to recognize a time field as elapsed time, not time of
day? I use this for race results, and the format is usually: h:mm:ss. When
I import from Excel, I am importing a field formatted as: [hh]:mm, even
though the [hh] is really minutes. When I do this, it converts any times of
24 minutes and above, e.g., 24:02 becomes 0:02. Basically, I want to import
into Access exactly what it sees in Excel without regard to the formatting.
I have tried everything and am out of solutions. Anyone out there? Thanks!