Dates are not in the same format even though both cells are formated as "DATE"

[The Mathguy]

I'm working with a big dataset and the dates come out in this way:

94WAO7t.png

How can I fix this?

EDIT: Example of book included

[FDibbins]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

I suspect that the 1's that dont come out "right" are text that just looks like a date. Formatting has no affect on the actual contents of a cell, just its appearance. Test using =ISNUMBER(cell-ref) FALSE indicates text

Let me know if this is the problem, then we can take the next step

[Pete_UK]

Some of those dates are text values and others are dates, but probably with the month and date transposed. How would you normally format a date in Denmark - is it dd/mm/yyyy or mm/dd/yyyy?

It might be better to post an example workbook rather than a picture of one. Then we can try out different formulae for you.

Hope this helps.

Pete

[The Mathguy]

The dataset is put together from different stand-alone sources where the format is mm/dd/yyyy but it would be nice to have it converted into the Danish date format dd/mm/yyyy.

Here is an example of the dates and the formats

http://www.docdroid.net/5abv/datetest.xlsx.html

[Pete_UK]

You can attach a file directly to a post in this Forum - here's how:

While you are editing a reply, you can click on Go Advanced in the bottom-right corner of the edit window and then scroll down to Manage Attachments and click on this. Then click Add Files, Select files, and navigate to your file through your folder structure and double-click its icon, then click Upload files and then Done, and then when you have finished editing your post just click Submit Post.

Hope this helps.

Pete

[AlKey]

click on the column with dates Go to DATA, Text to columns next, next select Date and click Finish

[The Mathguy]

I've included an example of my book in the original post.

The command ISTEXT returns that the dates which are to the left in the cells (first and last dates of book) are in the text-format while the dates which are to the right are not text

[AlKey]

Just follow the procedure in described in my post and this will fix the problem.

[FDibbins]

AlKey's suggestion will convert them for you, highlight the entire range (even the real dates)

[Pete_UK]

Alkey, & FD:

No, that only works for you because the dates were originally in US format.

Mathguy:

Put this formula in B1:

=IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))

format as a date in the style your prefer, then copy down. You can fix the values and then delete column A.

Hope this helps.

Pete

[The Mathguy]

That worked! Thank you so much