Viewing 15 posts - 1 through 15 (of 15 total)
  • Excel help please! Time stuff..
  • seadog101
    Full Member

    I have a spread sheet that has some times in it. Some of the times have been entered as 4 fig numbers “1230” not as “12:30”

    How can I get excel either convert the times to the correct format, or recognise the 4 fig numbers as times.

    I’m going to put data validation on the cells to stop the other numpties doing this in future…

    mikewsmith
    Free Member

    Find the person who did it, explain their mistake tell them to fix it.

    seadog101
    Full Member

    I’ve tried that. They look at me as though I have two heads and don’t seem to understand how useless they are.

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    rondo101
    Free Member

    if cell containing time = A2

    =if(len(A2)=4,left(A2,2)&”:”&right(a2,2),a2)

    copy, paste special, values
    highlight column, text to columns, finish
    change formatting for column to time

    lerk
    Free Member

    I always have time inputs as HHMM format, then do a conversion on that in the background.

    Once you’ve sorted the HHMM problem, you’ll no doubt have to solve the HH.MM, HH;MM and HH,MM problem too!

    mikewsmith
    Free Member

    not forgetting the HMM, HM and any other combination probably including some text ones…

    ampthill
    Full Member

    Make a table of numpty input verses correct out put

    1230 12:30
    1231 12:31

    then use vlookup

    But I think that you want to find and replace, not add a column. That would need a macro

    The table could contain other options like comma seperator

    DougD
    Full Member

    it depends on how they’ve written the times, have they used 24hrs? i.e. 1345 not 145?

    if 24hrs:
    cell containing time = A1
    =IF(LEN(A1)=3,((LEFT(A1,1)&”:”&RIGHT(A1,2))),((LEFT(A1,2)&”:”&RIGHT(A1,2))))

    this will take into account if am times have been entered as 3 digits e.g. 930, then highlight column, copy and paste special values, set format as time

    seadog101
    Full Member

    @ DougD and Rondo

    I get what you’ve both done there! thanks I’ll figure out how to handle the number from your suggestions. Fortunately they are all 4 figure 24hrs format numbers.

    muggomagic
    Full Member

    Also use data validation on the input cells, so in future they will have to enter it in the correct format.

    spawnofyorkshire
    Full Member

    =time(hour(left(a1,2)),minute(right(a1,2),0)

    that’ll just drop it straight into time format without having to paste anything more

    slowoldman
    Full Member

    I don’t think you want “hour” and “minute” in there. Just
    =TIME(LEFT(A1,2),RIGHT(A1,2),0)

    Oh and if you want 24 hour clock, check the cell format is hh:mm

    spawnofyorkshire
    Full Member

    @slowoldman you’re right, i’m used to pulling times out of the serial number in excel and put that in on autopilot
    OP – what Slowoldman did right there will do you

    smartboy
    Free Member

    Change the input cells to limit what users can enter.

    Use data validation so users pick an hour value from a drop-down list in one cell and minutes from a drop-down list is another cell.
    Can then concatonate the two and format it however is required.

    seadog101
    Full Member

    Thank you all. Now I feel a bit more knowledgucated about the TIME, LEFT and RIGHT functions. Stand well back while I use this to devastating effect.

Viewing 15 posts - 1 through 15 (of 15 total)

The topic ‘Excel help please! Time stuff..’ is closed to new replies.