Change format of lots of data cell to a time format

Hi All

I quite often get sent spreadsheets with lots of swimmers times on them but the creator of the sheets have not been using the correct format for time.

As with swimming we very rarely go over into hours I always format my columns as MM:SS.00, the 00 being 10ths and 100ths, but most of the time I get sent the times formatted with . instead of : between the MM & SS so it is sent to me as 5.17.86 instead of 5:17.86 if I then change the format of the column it ends up with a very strange time as you would expect.

could a macro be written to scroll through a range of columns and change them from 5.17.86 to 5:17.86?

just to make things a bit more complicated in some races the swimmers are quicker than 1 minute and again the in putter has stuffed up again as the resulting format should be 00:32.19 but they have only supplied a format of 32.19 obviously this will cause an issue with any macro that wants to just change a . to a : or look at the first 2 digits then insert a : and so on.

Oh and to make things more complicated there are some cells within columns that are blank and will need to stay blank

I have written some macros over the years but this is quite a bit above me I think, Cant even think where to start.

I may also be being really stupid as there maybe a very easy way to do this using different calculations and formats, if there is please enlighten me.

The attached sheet shows the wrong format in red and changed (by hand) in green for you to see on the sheet. the whole point of it is to be able to calculate differences in time on this particular sheet but occasionally I need to do more with the times, using my format of MM:SS.00 I can do all the calcs I require.

I sometimes get sent 4 or 5 sheets with over 1000 times to sort so really need to find a way of doing this, it can either just over write the original time or create another column to put the changed time into it.


Thanks in advance

Darren

Try this:
This macro works on selected range of cells.

Please Login or Register  to view this content.

Hi Guys thanks for the reply s and so quick!

I ran this one first on the sheet I supplied, worked well then on another sheet I had been given, unfortunately the creator of the sheet must have been having a bad time as he/she has managed to make some cells (not many) in the correct format to start with(MM:SS.00) when this macro then runs on these particular cells it changes the value in the cell to some strange number (excel obviously knows what its doing I don't though)

Is it possible to add a check to see if its in the correct format and leave it if it is?

I have scrolled through the code and played a little but cant quite get it, the code looks very short and easy but I cant quite get my head around it.

Thanks again

Darren

Originally Posted by hafizimran

Try this:
This macro works on selected range of cells.

Please Login or Register  to view this content.

Darren,
You can also built a data validation check on your co-workers workbooks, so that they would not be able to enter a data if they are not in the right format. I know validation is not fire proof but, can help you reduce this formatting errors.

Originally Posted by AB33

Darren,
You can also built a data validation check on your co-workers workbooks, so that they would not be able to enter a data if they are not in the right format. I know validation is not fire proof but, can help you reduce this formatting errors.

Thanks again for your reply, the slight issue I have is I don't generally supply a master file to others, they normally make there own and send them to me to be worked on.

I may be able to setup a template to send out to others but I think it may be difficult to get a 1 sheet fits all approach.

Could you give me an example of the data validation you are thinking of?

I would still like to get the original macro working as this would then be a good bit of code for me to use to save me time when they send me the incorretly formatted sheets

cheers

Darren

Uncomment the " And c.NumberFormat....."

Please Login or Register  to view this content.

Go to data-data validation, choose time, or date from the list and enter your input warning message.

Hi Again

Just tried this and it seems to be looking at the format of the cell rather than the format of the figures in the cell, so if the actual cell format is correct but the numbers show as 30.98 then it will not change the numbers to the correct format.

Do you have any other ideas?

I also found another issue when using the original formula last night, if the figure prior to change has 00 at the end it skips past it too so if I have 41.00 it sets "t" as 0 and then skips past as "t" is neither "1" or "2"

In 4 sheets I had 3 times that ended as 00 but would still like to get over this if possible.

Cheers

Darren

Originally Posted by hafizimran

Uncomment the " And c.NumberFormat....."

Please Login or Register  to view this content.

I need some time please.

Hi Jindon

I have downloaded this file but am concerned to use it as it is locked so I can not see the macro code, could you either post the code for me to see or unlock the code or give me the password.

I see it says you are a guru on the site so I should probably just trust it but I am to concerned for that sorry.

Darren

Originally Posted by jindon

..........

Hi Did you manage to look further into this for me?

Anyone else got any ideas?

cheers

Darren

Originally Posted by hafizimran

I need some time please.