Is there a function available to convert time(hours and minutes) to a number?
I'll explain: for my calculations about electrical energy I need to find the mean power which is obtained dividing energy expressed as kWh by the time length that energy was produced. For example if energy equals 22500 kWh and time length equals 7 hours 53 minutes, in order to find the mean power I have to convert 7:53 to 7,88.
Converting time to a number
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting time to a number
A time is stored as a number of days, e.g. 12 hours = 0.5, and 6 hours = 0.25
To convert to a number of hours, multiply by 24, so for example if the time field is named Duration, you can use
[Duration]*24
To convert to a number of hours, multiply by 24, so for example if the time field is named Duration, you can use
[Duration]*24
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Converting time to a number
But...if [Duration]= 7:53(7 hours and 53 minutes), what do I get by multiplying 7:53*24?
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting time to a number
The easiest way to find out is to try it out yourself - it takes less than a minute!
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Converting time to a number
Thanks Hans!! :-)
I would have never imagined that would so easily work using a form. But, outside of a form, if, for example you need to do the calculation in a query or in code, is there a way to do it so easily? If I go in the VBA immediate window and type ?07:53*24 I get an error. In these circumstances do I need a function that takes 7:53, turns it into a string, separate the various components, and reassemble the results into a number(7,88 in this case)?
I would have never imagined that would so easily work using a form. But, outside of a form, if, for example you need to do the calculation in a query or in code, is there a way to do it so easily? If I go in the VBA immediate window and type ?07:53*24 I get an error. In these circumstances do I need a function that takes 7:53, turns it into a string, separate the various components, and reassemble the results into a number(7,88 in this case)?
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting time to a number
In a query it would work exactly the same: [Duration]*24
In code it would work exactly the same: Duration * 24
If you want to test with a literal time, you must enclose it in # otherwise VBA won't recognize it as a time value.
In code it would work exactly the same: Duration * 24
If you want to test with a literal time, you must enclose it in # otherwise VBA won't recognize it as a time value.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Converting time to a number
Forgot the hashes!!!
And without even using a function!! You're a God!!!
And without even using a function!! You're a God!!!
-
- Her Majesty
- Posts: 499
- Joined: 19 Jan 2010, 07:13
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Converting time to a number
Hi Charlotte!!! (where's a smilie for a bunch of flowers when you need it?)
Yes, the Master reeled me in so the family reunion is ongoing
Yes, the Master reeled me in so the family reunion is ongoing