How can i Hard format the date type

Copper Contributor

Hello there,

i have an excel file of customers which i want to save as .xlsx and then import it to notion.
Since i live in germany and the standard date format here is DD.MM.YYYY i need to change it to MM.DD.YYYY because otherwise notion won't understand it.
So i tried changing the format through ctrl + 1 and then choosing the american standard.
My only problem is that excel will only change the displayed date and not the actual combination of letters saved in the cell. It stays DD.MM.YYYY as a saved value.
Is there a way to apply a hard format to the saved value in the cell and not only the display time of the value? 

2 Replies

@ReinholdBut 

Excel will always display a date in the formula bar in the user's short date format, regardless of the date format displayed in the cell itself.

So today, you would see 26.01.2022 in the formula bar, but if you sent the workbook to me, I'd see 2022-01-26, and someone in the USA would see 1/26/2022.

If you use a date format marked with a * in the Format Cells dialog, the format in the cell will be adjusted to the user's system settings automatically.

S1095.png

@Hans Vogelaar 

Thanks for the insights.
I managed to accomplish my goal with your tip of setting the system date format to the USA format and then using the "text to column" function of excel.
With this i got a bland text that the other program, notion, was able to interpret.
But i can't believe this is the intended way of doing this.