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 .

 

[awoohaw]

Yeah I think the design just sucks since date 12.08.2021 is returned as 0,528946759259259 and date 14.12.21 as 0,61505787037037. Or does that make any sense?

column b, rows 3,7,11,14 are not in the dd.mm.yyyy format. so the formula will not work.
Does it work for the other cells?

 

[awoohaw]

Yeah I think the design just sucks since date 12.08.2021 is returned as 0,528946759259259 and date 14.12.21 as 0,61505787037037. Or does that make any sense?

no. as I said... you need to put the formula in a new column, and make sure its pointing to the TEXT values that "look like" dates in the dd.mm.yyyy format.

 

[ExcelJay4]

The formula you gave (in L column) returns these dates as dates but these decimals which come out as 00.01.1900 will come out as #VALUE!.

 

[ExcelJay4]

No other changes made except formula to K column. No formats changed yet.