Time seperated by a decimal point

S

stevembe

Well-known Member
Joined
Mar 14, 2011
Messages
501
I have a spreadsheet where somebody has entered times separated by a decimal point and not a colon e.g. 9.15, 16.10 when they should read 09:15 and 16:10 etc. Is there any quick way to convert these to correct time format?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
stevembe said:
I have a spreadsheet where somebody has entered times separated by a decimal point and not a colon e.g. 9.15, 16.10 when they should read 09:15 and 16:10 etc. Is there any quick way to convert these to correct time format?
Click to expand...
just a little followup question, is there other cell that uses the period like text string with periods in it. If none then you can just use the CTRL+H
or the find and replace dialog.
find the period and replace with colon.
 
Last edited:
Upvote 0
stevembe said:
I have a spreadsheet where somebody has entered times separated by a decimal point and not a colon e.g. 9.15, 16.10 when they should read 09:15 and 16:10 etc. Is there any quick way to convert these to correct time format?
Click to expand...
Hi stevembe,

Have you tried selecting the column or whatever, pressing Ctrl+H to open the Find / Replace window, then replacing . with : and clicking Replace All?
 
Upvote 0
Fishboy said:
Hi stevembe,

Have you tried selecting the column or whatever, pressing Ctrl+H to open the Find / Replace window, then replacing . with : and clicking Replace All?
Click to expand...
Thanks for your responses, I did think of this but the times go strange e.g. 4.30 becomes 04:03 when it should be 04:30
 
Upvote 0
stevembe said:
Thanks for your responses, I did think of this but the times go strange e.g. 4.30 becomes 04:03 when it should be 04:30
Click to expand...
That.....is odd.

Having tried it myself you are quite correct. I will have to do some more testing and will hopefully get back to you shortly.
 
Upvote 0
I did also try
Code: 
=SUBSTITUTE(A3, ".",":")
but 11;23 returns as 0.47430555
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

cannotunderstandexcel
  • Solved
Referencing
2
Replies
11
Views
426
Peter_SSs
Peter_SSs
S
  • Solved
forcing a specific format into an excel cell [not used for calculation]
Replies
9
Views
524
sjoerdbosch
S
matrix26
  • Solved
VBA Macro
Replies
6
Views
455
Peter_SSs
Peter_SSs
T
  • Solved
Automatically insert a decimal point
Replies
6
Views
503
Joe4
Joe4
F
  • Solved
Issue with VBA code I created for work
Replies
7
Views
629
KAPOK
K

Forum statistics

Threads
1,214,641
Messages
6,120,694
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top