Excel help

Mark M · 7 Mar 2017 at 13:35

Not sure if this is achievable but Ive converted a number of pdfs to Excel and the only issue I have is the date has been imported as text and not formatted as a date. If you click into the cell and hit enter it automatically converts it to the correct date format. Is there any way to achieve this without having to click into every cell?

The other issue is that the source document only has a date when it changes day so you might have 6 entries on 6 different rows but only the top row will have the date. Is there any way to automatically copy the date from above if there is nothing in the cell? Im guessing this one might need some VBA?

Any help really appreciated.

Lmg80 · 7 Mar 2017 at 20:09

are the dates in the same column - text to columns would work as you can define the format

fullmetaltony · 8 Mar 2017 at 10:57

As far as changing format of date cells:
Highlight all the cells, Ctrl+enter?
Or, highlight all the cells formatted incorrectly and right-click "Format cells", you can set them to date format all at once then.

The blank cells can be easily fixed by inserting a column to the right, in the first cell of that column use (assuming D2 is first cell and C2 definitely has a date in it) =if(C2="",C1,C2), then copy that formula all the way down the column D, this should fill in all the blanks, you just need to copy and paste column D back in as "Paste values" and remove column C. There may be easier and quicker ways of doing it, but that's what I've been doing for years and you get pretty quick at it.

Mark M · 10 Mar 2017 at 10:51

Lmg80 said:
are the dates in the same column - text to columns would work as you can define the format

That works a treat thanks!

fullmetaltony said:
As far as changing format of date cells:
Highlight all the cells, Ctrl+enter?
Or, highlight all the cells formatted incorrectly and right-click "Format cells", you can set them to date format all at once then.

The blank cells can be easily fixed by inserting a column to the right, in the first cell of that column use (assuming D2 is first cell and C2 definitely has a date in it) =if(C2="",C1,C2), then copy that formula all the way down the column D, this should fill in all the blanks, you just need to copy and paste column D back in as "Paste values" and remove column C. There may be easier and quicker ways of doing it, but that's what I've been doing for years and you get pretty quick at it.

I couldnt get the Ctrl+Enter to work, doesnt seem to do anything?

The formula for the missing dates works if there is only one date missing but in the data Ive got there can be multiple empty cells which then just picks up the blank cell from row C in your example. I'll have a play around with it as Im sure I can get it to work.

Edit - managed to get it working using =IF(A3="",B2,A3) where A has the original dates in. You just need to copy the first date from A2 into B2 if that makes sense!

fullmetaltony · 10 Mar 2017 at 12:53

Yes, that makes sense, well done for spotting the intentional mistake in my formula

J/K - I came up with it in a flash, and should have tested it first to find the mistake myself, my bad.
Glad you have a solution. There's always likely to be tidier or more efficient solutions, but I find something that works in excel and look no further usually. It's rare my solutions don't involve vlookup, it's amazing how much time I save with that.