1

We are encountering strange issue while updating date column in SharePoint online excel files.

When the dates are entered using dots in between the date and month( like 21.10 , 18.5),in excel it is displaying as 1/21/1900 and 1/18/1900. This applies for any dates entered using dots between DD and MM. Is this a bug?

enter image description here

This issue doesn’t occur if the dates are entered using a – (like 21-10,18-5) or writing down the month alphabetically(like 21-Oct, 18-May).

1

It 's the default behavior that we cannot use dots between day and month in the Data format cell in Excel.

Using dots just performs as a number which add to the date from 1/1/1900.

0

In Excel, a date is the number of days since 01/01/1900 (which is the first date in Excel). For example, when we enter 18.5, it means 18.5 days since 01/01/1900. So it will be displayed as 1/18/1900 12:00:00PM. So when we enter 21.10, it will be displayed as 1/21/1900 2:24:00 AM.

However, when we enter 21-10 or 18-5, the contents of the cell will be Text and not a number. Excel will not think it's a date. It's easy to distinguish: In Excel, text is always displayed on the left of a cell. A number or a date is displayed on the right.

Reference:

https://www.excel-exercise.com/date-format-in-excel/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.