How do I convert time (hh:nn:ss) into the total minutes?

  • Thread starter Thread starter Cynthia
  • Start date Start date
C

Cynthia

So I am trying to create a little billing invoice from our phone data - when
we down load the file from the phone system we get the amount of time spent
on the call as hh:nn:ss - I need to manipulate this into minutes? (Note" the
field with the time is called duration :)
 
So I am trying to create a little billing invoice from our phone data - when
we down load the file from the phone system we get the amount of time spent
on the call as hh:nn:ss - I need to manipulate this into minutes? (Note" the
field with the time is called duration :)
Click to expand...

Access Date/Time fields are best used for specific points in time rather than
durations. Under the hood, a date/time value is stored as a Double Float
number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. As such a time value of 13:15:00 is actually stored as
0.54184027777777.

You can convert into minutes by dividing by the number of minutes in a day
(1440). Use the \ integer divide operation if you want integer minutes.
 
John,

I'm puzzled. Did you mean multiply the time by 1440 to get the number of minutes?
#13:15:00# * 1440 returns 795 which is the number of minutes since midnight

And when I multiply the time #13:15:00# by one I get 0.552083333333333

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I'm puzzled. Did you mean multiply the time by 1440 to get the number of minutes?
#13:15:00# * 1440 returns 795 which is the number of minutes since midnight
Click to expand...

Exactly... thanks for the catch, John, it had been too many minutes since I'd
had coffee!
 
This sounds great - but I am new to manipulating time - where do I put the
Select stmnt?

PieterLinden via AccessMonster.com said:
Cynthia said:
So I am trying to create a little billing invoice from our phone data - when
we down load the file from the phone system we get the amount of time spent
on the call as hh:nn:ss - I need to manipulate this into minutes? (Note" the
field with the time is called duration :)
Click to expand...

SELECT TimeCard.TimeIn, DateDiff("n",#12/30/1899#,[TimeIn]) AS Mins
FROM TimeCard;
Click to expand...
 
As long as the field contains only a time, you can multiply that time by 1440
to get the minutes. If the field contains seconds also then you might get
fractional parts of a minute. You can decide if you want to truncate the
partial minutes or not using the int function

The expression
Duration * 1440
will give you the number of minutes and partial minutes if any seconds

The expression
Int(Duration * 1440) will truncate the partial minutes

The expression
Round(Duration * 1440,0)
will round up or down the minutes

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Ok so I used =Int([duration]*1440) and the outcome is 12:00:00 AM for each
entry - any ideas why it would do this?
 
Since you did not show us where or how you used the expression, it is really
difficult to diagnose.

Is Duration a FIELD in your table or query?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
OK - am trying to create a report to invoice for each long distance code
entered, duration is a field in my phone data table(duration properties are
set to Format: hh:nn:ss )
- so I have made a query to filter for the data I need show on the report
- within the report I have created a field that is called Time, I put the
calculation into the Time field; The report is running off the query



John Spencer said:
Since you did not show us where or how you used the expression, it is really
difficult to diagnose.

Is Duration a FIELD in your table or query?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ok so I used =Int([duration]*1440) and the outcome is 12:00:00 AM for each
entry - any ideas why it would do this?
Click to expand...
Click to expand...
 
Figured it out - I created a new field in my query called Length and defined
it as the equation :)

Thanks for the reccommendation


Cynthia said:
OK - am trying to create a report to invoice for each long distance code
entered, duration is a field in my phone data table(duration properties are
set to Format: hh:nn:ss )
- so I have made a query to filter for the data I need show on the report
- within the report I have created a field that is called Time, I put the
calculation into the Time field; The report is running off the query



John Spencer said:
Since you did not show us where or how you used the expression, it is really
difficult to diagnose.

Is Duration a FIELD in your table or query?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ok so I used =Int([duration]*1440) and the outcome is 12:00:00 AM for each
entry - any ideas why it would do this?
Click to expand...
Click to expand...
Click to expand...
 
Use TimeValue([duration]) * 1440

Int does the exact opposite of what you want: it removes the time, not the
date!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Cynthia said:
Ok so I used =Int([duration]*1440) and the outcome is 12:00:00 AM for each
entry - any ideas why it would do this?
Click to expand...
 
Back
Top