If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
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 |
#2
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
Cynthia wrote:
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; -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
On Mon, 20 Jul 2009 16:16:01 -0700, Cynthia
wrote: 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 W. Vinson [MVP] |
#4
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total 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 John W. Vinson wrote: On Mon, 20 Jul 2009 16:16:01 -0700, Cynthia wrote: 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. |
#5
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
On Tue, 21 Jul 2009 09:47:43 -0400, John Spencer wrote:
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! -- John W. Vinson [MVP] |
#6
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
This sounds great - but I am new to manipulating time - where do I put the
Select stmnt? "PieterLinden via AccessMonster.com" wrote: Cynthia wrote: 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; -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
So would I just multiply by field by 1440?
"John W. Vinson" wrote: On Tue, 21 Jul 2009 09:47:43 -0400, John Spencer wrote: 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! -- John W. Vinson [MVP] |
#8
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
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 wrote: So would I just multiply by field by 1440? "John W. Vinson" wrote: On Tue, 21 Jul 2009 09:47:43 -0400, John Spencer wrote: 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! -- John W. Vinson [MVP] |
#9
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
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" wrote: 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 wrote: So would I just multiply by field by 1440? "John W. Vinson" wrote: On Tue, 21 Jul 2009 09:47:43 -0400, John Spencer wrote: 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! -- John W. Vinson [MVP] |
#10
|
|||
|
|||
How do I convert time (hh:nn:ss) into the total minutes?
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 wrote: 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? |
|
Thread Tools | |
Display Modes | |
|
|