Excel date formatting issues

Question

hi

i have a spreadsheet with around 80 000 records. i add to this spreadsheet daily.

there are three columns with dates that i copy and paste from a different spreadsheet daily. these are formatted to the Short Date format and follow the sequence DD/MM/YYYY.

when i filter the date columns, the filter options show me a folder of 2020, then a folder for each month, then a tick box for each date. if a date is formatted differently, it appears outside these folders and is not registered as the same date as one inside the folder. if this happens, i need to go to these dates and manually change them to the standard format. after that, they appear inside the filter folder rather than outside of it.

my problem is that when i copy dates from another spreadsheet and paste them to the bottom of this spreadsheet, the format of dates from some of the older records change. as a result, the dates of those older records do not appear in the filter folder as described above. these older dates are then difficult to work with as they do not register as the same date that appears on other records. oddly, the dates that are newly pasted align to the standard format and do not give this problem

i have tried the different paste options and have tried reformatting all cells of my spreadsheet. i have also tried reformatting the columns of the other spreadsheet before copying these cells.

why is this happening? how can i stop it from happening?

i'm using MS Excel for Mac v16.40 (20081000)

Answer 1

Hi Amilcar,

Sorry for the late response. According to our research, we find that some date in your Sheet 2 is stored as text.

As you can see in the below picture, we can check this by using the function “IsTEXT”. 

 

In the image above A427 is not text, however, A428 is text. That’s the root cause of why they are not organized in the filter because the text won’t appear under Years > Months > Days in the filter.

And if you want to convert these “Text date” to real date, you may follow these steps as below.

1. Select a blank cell ( or the “D2” column named “values”), and type this formula “=IF(AND(ISTEXT(A2)=TRUE,IFERROR(VALUE(A2),0)=0),DATE(RIGHT(A2,2)+2000,MID(A2,4,2),LEFT(A2,2)),VALUE(A2))”, drag auto fill handle down to the cells needed this formula.

 

2. Change the cells format to “Short Date”.

 

If you need further help, please feel free to contact us.

Best Regards,

May

Answer 2

Hi Amilcar,

Thank you for posting your issue here.

Excel uses date serial numbers to store date and time information.

According to your description, your issue may cause by the different attributes of your date records. Some records in your Excel are not true ‘date’, there are not stored in date serial numbers. To check this, you can use the VALUE function. If the ‘date’ was a ‘real’ date this would have translated it into the date serial number. Otherwise, it means it is may a ‘text’ date.

For more information: Why won’t the format change for a date?

If your date record is not a ‘real’ date, but rather as text, you can use the DATEVALUE function to convert it to a real date.

For your information:How to convert text to date and number to date in Excel

If your issue persists, for further help, would you send me the following information via private message?

1.Two sample files of your Excel files, one is used to paste, another is used to copy.

2 Screenshot of your date format.

Best regards,

May

Answer 3

Hi Amilcar,

Appreciate for your efforts to try the suggestions and you can come back to reply to us at any time.

Best Regards,

May

Answer 4

hi

thanks for the response. i will try this method and let you know in a few days time

thanks again

regards,

Amilcar

Answer 5

Hi,

May I know have you solved your formatting issue?

If you need further help, feel free to contact us.

Regards,

May