Convert date formats

[wpryan]

Hi All, I have a file with a few hundred rows that include "enroll date" and "score date" that I have to import into an LMS. The date format I was given is in d.m.yy format, and the LMS requires mm/dd/yyyy format. Can someone help me with a formula to do that? Thanks in advance...

 

[Joe4]

Are your dates currently entered as Text or Date?
One way to tell is to try to change the Date format on the cell. If that does not change the values appearance, then you are probably dealing with text entries.

And is it safe to assume that the first two digits of all the years should be "20", or might you have any that start with "19"?
If you may have some that start with "19", please explain to us the logic we need to use to determine if the year should start with "19" or "20".

 

[wpryan]

Are your dates currently entered as Text or Date?
One way to tell is to try to change the Date format on the cell. If that does not change the values appearance, then you are probably dealing with text entries.

And is it safe to assume that the first two digits of all the years should be "20", or might you have any that start with "19"?
If you may have some that start with "19", please explain to us the logic we need to use to determine if the year should start with "19" or "20".

Hello, thanks for your reply. I was able to work it out by changing to a custom format.

 

[Joe4]

Hello, thanks for your reply. I was able to work it out by changing to a custom format.

Then it sounds like your dates are already entered as dates, and not text.
If that is the case, it is pretty easy to choose any format/custom format that you want.
Where most people run into trouble is when there dates are entered as text, and they need to convert them to dates before they are able to apply any custom formats to them (which is why I asked the question).