+ Reply to Thread
Results 1 to 12 of 12

Change format of lots of data cell to a time format

  1. #1
    Registered User
    Join Date
    04-13-2011
    Location
    Bicester
    MS-Off Ver
    Excel 2003,2010
    Posts
    37

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Change format of lots of data cell to a time format

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

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Change format of lots of data cell to a time format

    ..........
    Attached Files Attached Files
    Last edited by jindon; 03-16-2013 at 10:40 PM.

  4. #4
    Registered User
    Join Date
    04-13-2011
    Location
    Bicester
    MS-Off Ver
    Excel 2003,2010
    Posts
    37

    Re: Change format of lots of data cell to a time format

    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

    Quote Originally Posted by hafizimran View Post
    Try this:
    This macro works on selected range of cells.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Change format of lots of data cell to a time format

    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.

  6. #6
    Registered User
    Join Date
    04-13-2011
    Location
    Bicester
    MS-Off Ver
    Excel 2003,2010
    Posts
    37

    Re: Change format of lots of data cell to a time format

    Quote Originally Posted by AB33 View Post
    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

  7. #7
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Change format of lots of data cell to a time format

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

    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Change format of lots of data cell to a time format

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

  9. #9
    Registered User
    Join Date
    04-13-2011
    Location
    Bicester
    MS-Off Ver
    Excel 2003,2010
    Posts
    37

    Re: Change format of lots of data cell to a time format

    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
    Quote Originally Posted by hafizimran View Post
    Uncomment the " And c.NumberFormat....."

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Change format of lots of data cell to a time format

    I need some time please.

  11. #11
    Registered User
    Join Date
    04-13-2011
    Location
    Bicester
    MS-Off Ver
    Excel 2003,2010
    Posts
    37

    Re: Change format of lots of data cell to a time format

    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

    Quote Originally Posted by jindon View Post
    ..........

  12. #12
    Registered User
    Join Date
    04-13-2011
    Location
    Bicester
    MS-Off Ver
    Excel 2003,2010
    Posts
    37

    Re: Change format of lots of data cell to a time format

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

    Anyone else got any ideas?

    cheers

    Darren

    Quote Originally Posted by hafizimran View Post
    I need some time please.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1