## Converting time to a number

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

### Converting time to a number

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.

HansV
Posts: 74448
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
Regards,
Hans

grovelli
4StarLounger
Posts: 517
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?

HansV
Posts: 74448
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!
x17.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

grovelli
4StarLounger
Posts: 517
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)?

HansV
Posts: 74448
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.
x18.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

grovelli
4StarLounger
Posts: 517
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!!!

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

### Re: Converting time to a number

Hi Giorgio,

Glad you found Eileen's Lounge. Welcome.
Charlotte

grovelli
4StarLounger
Posts: 517
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