Changing decimal time to actual hours and minutes

[GRW87]

Hi everyone,

I've made an error at work in that I've populated a massive spreadsheet (of times in hours and minutes) by hand, but I used a . instead of a :

So for instance:

• 1 hour and 30 mins says 1.3
• 7 hours and 48 minutes says 7.48
• 3 hours and 50 minutes says 3.50

Etc. I can't obviously now add up all the times as 1.3 + 1.3 would = 2.6 instead of the 3 hours I want

Is there a way of fixing this mistake??

Thank you in advance!

 

[Declamatory]

Select all of your times and press Ctrl H.

Replace . with :

 

[ImranMalek]

Hi ,

you can write this formula =TIME(INT(A1), MOD(A1,1)*100, 0)


Hope it helps.

 

[GRW87]

Select all of your times and press Ctrl H.

Replace . with :

Thanks!

The only problem with that is 8.5 say (eg what I wanted to be 8 hours and 50 minutes) becomes 8:05 for some reason

Also 9 hours (written as simply 9) becomes 0:00

 

[Mackers]

You could give this a go if you want a text string of HH:MM that can go over 24 hours (not sure that's your requirement):

=LEFT(A2,IFERROR(FIND(".",A2)-1,LEN(A2)))&":"&MID(A2&"00",IFERROR(FIND(".",A2)+1,LEN(A2)+1),2)

 

[Declamatory]

Thanks!

The only problem with that is 8.5 say (eg what I wanted to be 8 hours and 50 minutes) becomes 8:05 for some reason

Also 9 hours (written as simply 9) becomes 0:00

Ah right. I'm not sure how to fix that.

As a side note I don't think you want HH:MM as a format. If you custom format the cell as 00":"00 then when you type in 748 the cell will show 07:48. 900 would show as 09:00 This makes it easier both to input and when adding up hours and minutes.

I think you could fix it if you were able to insert a column next to the time column so as to insert a formula, then convert the cells to numbers and format. I'll run through the process with you if this is something you can do.

 

[Declamatory]

First select your times column and press CTRL + H. Replace the decimal point with nothing. This will convert the cells from 1.3 to 13 & 7.48 to 748 etc.

Insert a column next to the time column and insert a formula as follows (assumes times are in column A) =IF(LEN(A2)=1,(A2)&"00",IF(LEN(A2)=2,(A2)&"0",IF(LEN(A2)=3,(A2),IF(LEN(A2)=4,(A2),))))

Then highlight your column with formulas, copy and paste special values and number formats.

The numbers in your column are now in a text string. If you highlight your new column you should get a little exclamation mark in a yellow box that pops up next to your highlighted column. Click on the exclamtion mark and a dropdown list should appear. Select convert to number.

The highlight the new column and format the column. Click on custom and in the box type 00":"00

The cells will now be in the format you want. Copy the new column and paste over the column that had the times in.

Then delete your new column that you added.

 

[GRW87]

Wow! Thank you so much... that was about 5 hours worth of work and I was panicking I'd have to do it all again, so you've helped me a lot. Much appreciated, it's all sorted now ?

 

[Declamatory]

Glad I could help.

Thinking about it If you sum the total number of hours and minutes it will look odd if the number of minutes is over 60. e.g. if the number of hours and minutes could end up looking something like this 17:78 instead of 18:18.

So where the sum of hours and minutes might have been =SUM(A1:A3) it should be replaced by =IF(RIGHT(SUM(A1:A3),2)>60,LEFT(SUM(A1:A3),2)+1&":"&(RIGHT(SUM(A1:A3),2)-60))