Insert cell data

Hi,

I'm looking for a formula that will insert "20" in front of the "18" (to make 2018) only if its not already there(as in cell A1)

A
1 04/12/2018
2 04/13/18

Any help appreciated.

Doug

Try

select data

Data>>>Text to Columns>>>Step 3 ..Date>>MDY (or DMY)..Finish

I had tried that previously John, the column with dates in is a mixture of date and text format , not sure why ,it seems its a fault on my operating system?
So a formula is my only real way of rectifying...

It would help if you attached a sample Excel workbook, so we can see how your data varies.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon (Attachments button) does not work on this forum, so don't try to use that.

Hope this helps.

Pete

Attachment

Change your calculation mode to Automatic, rather than Manual, then you can use this formula in C1:

=IF(ISNUMBER(A1),A1,DATE(2000+RIGHT(A1,2),LEFT(A1,2),MID(A1,4,2)))

Format that cell as a date, then copy the formula down as far as you need to.

If you want those converted dates back in column A, then you can fix the values in column C and copy/paste them to A, then delete column C.

Hope this helps.

Pete

thanks for the help.

Doug