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]

Yes region is Finland. Regional format is English (Finland). Calendar: Gregorian Calendar. Short date: 16/03/2023.

 

[awoohaw]

so you should only enter dates as dd/mm/yyyy. Even if the cell is formatted differently. Excel will then change how it is displayed.

 

[ExcelJay4]

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

So there is no way of using dd.mm.yyyy format when regional format in windows is dd/mm/yyyy?

 

[awoohaw]

So there is no way of using dd.mm.yyyy format when regional format in windows is dd/mm/yyyy?

No... you can format the cells that way. I just don't think you can enter the date that way.

 

[ExcelJay4]

Hope this picture clarifies my problem a bit.

 

[ExcelJay4]

No... you can format the cells that way. I just don't think you can enter the date that way.

Thank you. Not anymore excel question but I'll try. When going to windows regional setting for short date it shows 2 options 16/03/2023 or 16 Mar 2023. So there seems to be no way for me to have the basic formatting as dd.mm.yyyy...

 

[ExcelJay4]

So basically if there is somekind of formula to change the the decimal of the date to date it would also help.

 

[awoohaw]

So basically if there is somekind of formula to change the the decimal of the date to date it would also help.

what do you mean? Are you importing data that is dd.mm.yyyy? or pasting?

My suggestion would be to import or paste this into cells formatted as TEXT. Then use this formula to covert it into a date value (in any cell not formatted as text):
(assuming the date that was pasted as text is in column B starting in row 2):
=DATE(RIGHT(B2,4),MID(B2,FIND(".",B2)+1,FIND(".",B2,FIND(".",B2)+1)-FIND(".",B2)-1),LEFT(B2,FIND(".",B2)-1))

To see it more clearly:
=DATE(RIGHT(B2,4),
MID(B2,FIND("/",B2)+1,FIND("/",B2,FIND("/",B2)+1)-FIND("/",B2)-1),
LEFT(B2,FIND("/",B2)-1))

 

[ExcelJay4]

Okay last try. So the goal is to get these decimals to dd.mm.yyyy.

 

[ExcelJay4]

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?