I have list of start times that have been entered in the format 10.01 etc I need them to be in time format in order to use them in a spreadsheet to produce an event schedule, is there an easy way to convert the whole column so it reads 10:01 etc without overtyping it all, I tried to just change the cell format but this changes the value to :14 and so on.
Thanks
Last edited by suedavies; 03-16-2015 at 08:40 AM.
have you tried custom formatting? Like h:mm?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
I presume these times are in seconds correct?
Is 10.05 supposed to be 10 Minutes and 5 seconds, or 10 Hours and 5 minutes?
Try this if it's Hours and Minutes
=SUBSTITUTE(A1,".",":")+0
Or this if it's Minutes and Seconds
=SUBSTITUTE("00:"&A1,".",":")+0
Format the cell with the formula as a Time.
Another way to do it assuming you've got minutes and seconds there:
or hours and minutes
Please Login or Register to view this content.
Please Login or Register to view this content.
Hi,
No the start time is the hour and minute, so they start the event at one minute past ten and so on, I tried the HH:mm custom format but that then changes 10.01 to :14 rather than 10:01
Thanks Sue
Thanks Jonmo1 works great
so then have you tried using find . and replace all with : ?
Sambo kid thanks so simple why didn't I think of that spent hours Saturday overtyping a huge list
sometimes you get your brain fried trying to think of ways to solve something and overlook the simpliest because you aren't thinking straight. Anyway that has happened to me a few times.
And thanks for the reputation points.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks