One more TIME

  • Thread starter Thread starter angelo325
  • Start date Start date
A

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
 
Hi Angelo

Here's a formula that returns 7:20 from 7.2 in A1:
=TIME(INT(A1),100*(A1-INT(A1)),0)
 
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
 
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.)
 
Dave
It worked!!!!!

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


Angelo



Dave Peterson said:
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
Click to expand...
Click to expand...
 
Dave Peterson said:
Try:

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

and format your cell as: [h]:mm
Click to expand...

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.
 
I never noticed it, either.
(Excel's Help ain't so bad!)

Harald said:
Dave Peterson said:
Try:

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

and format your cell as: [h]:mm
Click to expand...

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.
Click to expand...
 
Back
Top