want convert time from 1217.50 into 12:17:30

My data is in the wrong format. The first 2 digits are hours. The next 2 are minutes. I would like to put a : in between them

How do I do this?

Secondly I would like to convert the decimal minutes into second and put a : between them as well.

Can anybody help?

Try

=TIME(MID(A1,1,2),MID(A1,3,2),MID(A1,6,2)*1)

just little modification in fotis1991 ans.
use this
=TIME(MID(TEXT(A1,"0000.00"),1,2),MID(TEXT(A1,"0000.00"),3,2),MID(TEXT(A1,"0000.00"),6,2)*6/10)

in this i assume that after decimal it is go up to 99 so in that case 100 will b count as 60 seconds....

Thank you for your quick response. However I don't know how to use these answers. I'm not a very good programmer

In cell D9 the number 1217.50 is displayed. I want to keep this figure. however on another part on the spreadsheet I would like to display it as 12:17:30

Do I write =D9(MID(TEXT(A1,"0000.00"),1,2),MID(TEXT(A1,"0000.00"),3,2),MID(TEXT(A1,"0000.00"),6,2)*6/10)

No worries! Take a look to the example.

Ok. Now i got what you need. Use pwnyadav007,s suggested formula.

See the example.

=TEXT(INT(A1),"00\:00")+MOD(A1,1)/1440 should do it

Originally Posted by pwnyadav007

just little modification in fotis1991 ans.
use this
=TIME(MID(TEXT(A1,"0000.00"),1,2),MID(TEXT(A1,"0000.00"),3,2),MID(TEXT(A1,"0000.00"),6,2)*6/10)

in this i assume that after decimal it is go up to 99 so in that case 100 will b count as 60 seconds....

This works great on a spreadsheet. However it won't work when I add it to my existing spreadsheet. Don't know why

I'm making a calendar that will find true north. Simply input the longitude and the standard meridian for the timezone

Example I'm at 96.63 longitude. The standard meridian for this time tone is 90 longitude

On Jan 1 at 1229.83 a string line on a plumb bob will cast a shadow that points to true north

I want to keep the existing table with the time of 1229.83. However I want another table that converts this to 12:29:49 (shown in green)

The formula above works great but for some reason, not on this spreadsheet.

i think that formula is slightly inaccurate as 0.83 mins is closer to 50 seconds than 49
and the actual value in that cell is
1229.8322976733700
which is even closer to 50 seconds than 49
=TEXT(INT(A1),"00\:00")+MOD(A1,1)/1440 gives the correct seconds
see attached and don't forget to format cells as hh:mm:ss