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

[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

 

[PieterLinden via AccessMonster.com]

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

SELECT TimeCard.TimeIn, DateDiff("n",#12/30/1899#,[TimeIn]) AS Mins
FROM TimeCard;

 

[John W. Vinson]

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

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 Spencer]

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

 

[John W. Vinson]

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

Exactly... thanks for the catch, John, it had been too many minutes since I'd
had coffee!

 

[Cynthia]

This sounds great - but I am new to manipulating time - where do I put the
Select stmnt?

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

SELECT TimeCard.TimeIn, DateDiff("n",#12/30/1899#,[TimeIn]) AS Mins
FROM TimeCard;

 

[Cynthia]

So would I just multiply by field by 1440?

Exactly... thanks for the catch, John, it had been too many minutes since I'd
had coffee!

 

[John Spencer]

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

 

[Cynthia]

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?

 

[John Spencer]

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

 

[Cynthia]

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

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?

 

[Cynthia]

Figured it out - I created a new field in my query called Length and defined
it as the equation

Thanks for the reccommendation

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

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?

 

[Douglas J. Steele]

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!)

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?