Converting time to a number

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Converting time to a number

Post by grovelli »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting time to a number

Post by HansV »

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
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Converting time to a number

Post by grovelli »

But...if [Duration]= 7:53(7 hours and 53 minutes), what do I get by multiplying 7:53*24?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting time to a number

Post by HansV »

The easiest way to find out is to try it out yourself - it takes less than a minute!
x17.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Converting time to a number

Post by grovelli »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting time to a number

Post by HansV »

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.
x18.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Converting time to a number

Post by grovelli »

Forgot the hashes!!! :hairout: :bash:
And without even using a function!! You're a God!!! :king: :artist: :bravo:

User avatar
Charlotte
Her Majesty
Posts: 499
Joined: 19 Jan 2010, 07:13

Re: Converting time to a number

Post by Charlotte »

Hi Giorgio,

Glad you found Eileen's Lounge. Welcome.
Charlotte

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Converting time to a number

Post by grovelli »

Hi Charlotte!!! :clapping: :fanfare: :cheers: :music: :hello: (where's a smilie for a bunch of flowers when you need it?)
Yes, the Master :gent: reeled me in so the family reunion is ongoing :hugs: