Trying to convert number to date and time. 0 is proving to be a problem. File attached

[jaywizz]

I am getting a basic extract of info from a server.

It is my intention to be able to get the date and time in on cell and then subtract the information from the date and time the report was run to highlight any events that are 20 minutes or more earlier than the report extract date and time.

I have attached a file to show (Excel 2010) that I get a column of dates showing 081112 (DD/MM/YY) and time as 051148 (HH/MM/SS).

I can't seem to format just the cell to show a date or time. As you see in the attached I have attempted a solution which seems to work unless the first char is a 0 which totally buggers it up.

Thanks in advance

Sample.xlsx

[Dave H9]

Hi Jaywizz,

Someone my come in with a simpler solution, but the attached seems to do what your after. Give it a go and let us know.

Dave H

[ChemistB]

What would a June 20, 2012 look like from the data base; 200512 or 20512?

[ChemistB]

Try this formula

=(IF(LEN(A3)=5,LEFT(A3,1)&"/"&MID(A3,2,2),LEFT(A3,2)&"/"&MID(A3,3,2)) &"/"&RIGHT(A3,2))+0
Does that work for you?

[kbkumar]

Hi - I have put a small code to get the proper dates (recognized by excel) in Column O:

Please Login or Register  to view this content.

Could you please let us know if this works for you. If if does, we can tweak it a bit to do factor the time as well.

Thansk,

[jaywizz]

Folks thats great all of your solutions worked. You have saved many lives

Thanks