One more TIME

[angelo325]

Hi all
After downloading data it is presented with a decimal(period) between
the hours and minutes i.e.7.20 is really 7 hours and 20 minutes. Some
data appears in hundreds eg 985.35.
I need to compare this data with other correctly formatted timed data
ie 7:20 to establish variances.

I realise that i could use Tools/Autocorrect replacing the decimal
with a colon but this changes the 7.20 to 07:02 and there is the
danger that I might forget to change it back later.
Is there a solution?
Can this be done automatically by macro etc as there is quite a lot of
data.

Thanks in advance.

Angelo

 

[Harald Staff]

Hi Angelo

Here's a formula that returns 7:20 from 7.2 in A1:
=TIME(INT(A1),100*(A1-INT(A1)),0)

 

[angelo325]

Harald
Cheers for the reply.
One thing though, some of the figures I need conversion are more then
24 hours e.g. I will have something like 351.20 which is 351 Hours and
20 minutes. Is there a way of tackling larger amounts?
These represent hours worked in a month and I have to compare them
with what was budgeted hence my desire to sort out the format before
executing the test.

Yours indebtedly

Angelo

 

[Dave Peterson]

Try:

=INT(A1/24)+TIME(INT(A1),100*(A1-INT(A1)),0)

and format your cell as: [h]:mm
(the square brackets tells excel that you don't want to overflow into days.)

 

[angelo325]

Dave
It worked!!!!!

Thank you very much.
Now to find a way to automate this ........


Angelo

Try:

=INT(A1/24)+TIME(INT(A1),100*(A1-INT(A1)),0)

and format your cell as: [h]:mm
(the square brackets tells excel that you don't want to overflow into days.)

Harald
Cheers for the reply.
One thing though, some of the figures I need conversion are more then
24 hours e.g. I will have something like 351.20 which is 351 Hours and
20 minutes. Is there a way of tackling larger amounts?
These represent hours worked in a month and I have to compare them
with what was budgeted hence my desire to sort out the format before
executing the test.

Yours indebtedly

Angelo

 

[Harald Staff]

Try:

=INT(A1/24)+TIME(INT(A1),100*(A1-INT(A1)),0)

and format your cell as: [h]:mm

So TIME does the 24 hr MOD thing -it never crossed my mind. Thanks for correcting this,
Dave.

Best wishes Harald
Excel MVP

Followup to newsgroup only please.

 

[Dave Peterson]

I never noticed it, either.
(Excel's Help ain't so bad!)

Try:

=INT(A1/24)+TIME(INT(A1),100*(A1-INT(A1)),0)

and format your cell as: [h]:mm

So TIME does the 24 hr MOD thing -it never crossed my mind. Thanks for correcting this,
Dave.

Best wishes Harald
Excel MVP

Followup to newsgroup only please.