How do I change 8.30 to 8:30 please?

[tammorice]

I have a spread sheet full of start & finish times that are in decimal (8.30am) format that I need to change to 8:30 so that I can use a formula to calculate the worked hours.

Start 8.30
Finish 12.13
Total Hours 'x'

Thanks
Tammie

 

[mole999]

I know if you divide by 24, and format as [h]:mm you get a result, but 0.3 of an hour is 18 minutes,
and 8.30am is not a decimal, its text
not a lot of help

do all your entries have am or pm or is that just a demonstration

 

[MarcelBeug]

If your time is in A1:

=INT(A1)/24+MOD(A1,1)/0.6/24

 

[sumantaimd]

You can use replace "." by ":" and "am" by " " which may be solve your problem first take a back up your master copy.

 

[tammorice]

No am/pm just columns with time e.g 8.30

 

[mole999]

answer 3 is the one you need I believe

 

[tammorice]

If your time is in A1:

=INT(A1)/24+MOD(A1,1)/0.6/24

Where do i write the code?

 

[pgc01]

Hi

With 8.30 in A1, in B1:

=DOLLARDE(A1;60)/24

Format the cell as time.

 

[tammorice]

You can use replace "." by ":" and "am" by " " which may be solve your problem first take a back up your master copy.

Huh....why didn't i think of that. Genius. Thank you.