Date sometimes changed into text (Excel 2010)

[Tino XXL]

Hi,

When I combine 3 reports (which we get out of a system) into 1 big file, the date format remains the same (mm/dd/yyyy and right alligned). Same happens for most of my colleagues.
When 1 particular colleague goes and combine these reports, I've noticed that some of the dates are showing as text? (dd/mm/yyyy and left alligned).

Can anyone explain me what's causing this issue?

Regards,
Tino XXL

[newdoverman]

My system is set up to use D/M/Y format and I have trouble importing dates in the M/D/Y format. They usually come in as text or as an incorrect date if the month and day are less than or equal to 12.

Check the system settings in the Regional settings in the Control Panel to make sure that they are the same as what the rest of you are using.

[Philb1]

Another thing I've noticed with csv files is the empty space character between the dates & time. Sometimes there is 1 space instead of 2 & on my system it formats as text. Another problem can be the formatting of the dates from dd/mm/yyyy to dd-mm-yyyy which also formats as text. You can use replace to look for single spaces & replace with 2 spaces & the same goes for - & /

[newdoverman]

If spaces are a problem, use the TRIM function to TRIM out the unnecessary spaces from the dates. Dates that don't have excess spaces will not be affected.