Solved

# Datatype for time

Posted on 2013-09-12
Medium Priority
452 Views
I want to import an Excel file that contains time in the format below.  I want to do calculations on time (like how many minutes),

00:05:36
00:04:19
00:02:42

Again, so I want to add this to access and be able to sum up and say, for example, that it was 3 hours or 25 minutes, etc...

I'm (1) not sure what data type and (2) how I may be able to sum up.

I attached a sample if it helps.
sample.xls
0
Question by:patriotpacer
• 4
• 3
• 3

LVL 74

Expert Comment

ID: 39486914
To be clear, what is the *exact* result you want for the values displayed...
0

Author Comment

ID: 39486946
>>All format types of Time in Access

Thank you

>>To be clear, what is the *exact* result you want for the values displayed...

Just be able sum them up would probably work.

60 MINUTES =

00:20:00
00:20:00
00:20:00
0

LVL 74

Assisted Solution

Jeffrey Coachman earned 1000 total points
ID: 39486952
1. Import the Excel File
2. Create a query like this:
SELECT Sum([YourMinutesField]) AS TotalMinutes
FROM YourTable;
3. Set the format property of this filed to: hh:nn:ss

This will give you:  0:12:37
...as the result for those numbers
(12 minutes, 37 seconds)
0

LVL 53

Expert Comment

ID: 39486964
Don't let you confuse; it's not about the format, it's about the data type.

And the data type for date and/or time in Access is Date.

/gustav
0

LVL 74

Expert Comment

ID: 39486968
...or you can create an expression:
DSum("YourMinutes","YourTable")

And use that as the controlsource for a textbox:
=DSum("YourMinutes","YourTable")
...Then set the format property of this textbox to:  hh:nn:ss
0

LVL 53

Accepted Solution

Gustav Brock earned 1000 total points
ID: 39486980
To sum time periods, just sum these.
If the total is within 24 hours, that's all.

If more then 24 hours, and you wish a display like 34:15, use a function like this:
``````Public Function FormatHourMinute( _
ByVal datTime As Date, _
Optional ByVal strSeparator As String = ":") _
As String

' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

Dim strHour       As String
Dim strMinute     As String
Dim strHourMinute As String

strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
' Add leading zero to minute count when needed.
strMinute = Right("0" & CStr(Minute(datTime)), 2)
strHourMinute = strHour & strSeparator & strMinute

FormatHourMinute = strHourMinute

End Function
``````
/gustav
0

LVL 74

Expert Comment

ID: 39487013
patriotpacer,

<I'm (1) not sure what data type and (2) how I may be able to sum up.>

When you import your table to Access, ...Access should set the data-type as "Date\Time".
However, it might display an odd date as well.
You can cure this by setting the format property of the field to: hh:nn:ss

Jeff
0

Author Comment

ID: 39487021
thanks.

I will review and test all the comments posted.

I really appreciate the help.
0

Author Comment

ID: 39488043
Thank you so much for all the help.

The function worked VERY well.
0

LVL 53

Expert Comment

ID: 39488466
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
###### Suggested Courses
Course of the Month13 days, left to enroll

#### 607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.