Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
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...
Find the person who did it, explain their mistake tell them to fix it.
I've tried that. They look at me as though I have two heads and don't seem to understand how useless they are.
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
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!
not forgetting the HMM, HM and any other combination probably including some text ones...
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
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
@ 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.
Also use data validation on the input cells, so in future they will have to enter it in the correct format.
=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
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
@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
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.
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.