Access 2003 How to convert hours - minutes - seconds  to minutes

Posted on 2006-11-13
Medium Priority
Last Modified: 2008-01-09

I import a text file that give me time formated h:mm:ss which is good.  I can see all calls for a day and have a total of say 5:19:23.  What I want to do is to convert h:mm:ss to only minutes?

How do I covert it to minutes??


Question by:bjennings
LVL 44

Expert Comment

ID: 17932664
=Hours(totaltime)*60 +Minutes(totaltime) + IIf(Seconds(totaltime)>29,1,0)
LVL 44

Expert Comment

ID: 17932671
In this case I condsidered the seconds portion of totaltime and rounded it up to 1 it 30 seconds or more.


Expert Comment

ID: 17932694
You didn't say what you wanted to do with the seconds.  This trucates.

Public Function HHMMSS_To_Minutes(ByVal sTime As String) As Integer
    Dim col1stColon As Integer
    col1stColon = InStr(sTime, ":")
    Dim iHours As Integer
    iHours = Left(sTime, col1stColon - 1)
    Dim iMins As Integer
    iMins = Mid(sTime, col1stColon + 1, 2)
    ' ignores seconds
    HHMMSS_To_Minutes = (iHours * 60) + iMins
End Function
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 16

Expert Comment

ID: 17933140
Sub ConvertTimeToMinutes()
    Dim tim As Date
    tim = #1:05:30 AM#
    Debug.Print tim * 1440
End Sub

If you want to round the result encapsulate the last line with the round function like this...

Debug.Print Round(tim * 1440,2)

In any case the short answer is take your date/time variable and multiply it by 1440.  The result will be the number of minutes that that date/time variable contains.  

Be aware, that trying this against a date like today's date will result in a very large number.  I presume that you're trying to deal with much smaller numbers (difference between two date & times but if not you may need to decide how many days in the date portion of the field you want to include.

LVL 44

Accepted Solution

GRayL earned 1000 total points
ID: 17933693
Sorry, remove the s from each of my functions:

=Hour(totaltime)*60 +Minute(totaltime) + IIf(Second(totaltime)>29,1,0)

Author Comment

ID: 17938564
Thank you so much everyone!!!  GRayl was the easiest solution!
LVL 44

Expert Comment

ID: 17939796
Thanks, glad I could help

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

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

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

Join & Ask a Question