Date format problem

[ExcelJay4]

Hey,

I have a problem where I can type dates dd/mm/yyyy but when I type it dd.mm.yyyy as I'm accustomed to it comes as a decimal. If I try to change the decimal to short date it appears as 00/01/1900. Appreciate your help .

 

[ExcelJay4]

Same problem when I download a excel file from software some dates appear as decimals and changing format to short date it shows the same problem -> 00/01/1900. For some reason the dates which are last day of the month e.g. 31.07.2021 appear as they are supposed to except their format is general but I can change them to short date format normally.

 

[awoohaw]

Did you format the cell in custom formatting as dd.mm.yyyy?

 

[ExcelJay4]

I type to my cell 15.08.2021. It comes as 0,653946759259259. If I format this to dd/mm/yyyy it comes off as 00/01/1900.

 

[ExcelJay4]

The formula bar even has time in there too . I open the format of the cell and it shows dd.mm.yyyy and it somehow shows the time also??

 

[awoohaw]

i'm confused.... you said you wanted dd.mm.yyyy format.
regardless: it must be done in the correct order
1. Format the cell dd.mm.yyyy or dd/mm/yyyy or mm.dd.yyyy or mm/dd/yyyy It doesn't matter..... just whatever format you want.
2. Input the date 15.08.2021 or 15/08/2021 or 08.15.2021 or 08/15/2021 (according to the format above.
... if that doesnt work Just put it in with the DATE Function. =Date(2021,08,15)

 

[ExcelJay4]

I know. This is the weirdest bug. I tried to open a new workbook, format the whole sheet as dd.mm.yyyy. After that i enter 15.08.2021 and it came out as 00/01/1900 19.42.41.

 

[awoohaw]

i'm confused.... you said you wanted dd.mm.yyyy format.
regardless: it must be done in the correct order
1. Format the cell dd.mm.yyyy or dd/mm/yyyy or mm.dd.yyyy or mm/dd/yyyy It doesn't matter..... just whatever format you want.
2. Input the date 15.08.2021 or 15/08/2021 or 08.15.2021 or 08/15/2021 (according to the format above.
... if that doesnt work Just put it in with the DATE Function. =Date(2021,08,15)

scratch the first part of step 2. when you type in the dates that way excel thinks it is text.
just use the Date(YEAR,MONTH,DAY) method.

 

[awoohaw]

Do you know what your WINDOWS regional settings are? (Not Excel... WINDOWS).

 

[awoohaw]

I think you need to enter dates either using the DATE function (as above) or in however your windows regional settings are established.