Excel 2021 - how to set format for date&time as 'dd.MM.yyyy HH:mm'?

[senglory]

Hi,

I've got an Excel workbook with column containing text which represents date&time as the specified format, i.e. 'dd.MM.yyyy HH:mm'. But this column is not recognized as a date&time, so I have to specify its format explicitly to be able to do sorting lines per this date correspondingly. When I try to open dialogbox 'Format cells', it gives me no opportunity to specify desired date&time format for the column. How could I assign necessary format for tis column in this case?

 

[Skybluekid]

Book1.xlsm
	D	E	F
2
3		02.01.2023 12:00
4
Sheet2

For the Cell, I have created a custom format dd.mm.yy HH:mm and it gives the expected result

 

[rollis13]

Maybe all you need to do is multiply by 1 all the data using PasteSpecial.

 

[Alex Blakenburg]

I've got an Excel workbook with column containing text

Since it is text, the easiest way is to select the column and use replace to replace the period "." with the "/".
This should convert it to a date and you should be able to use the formatting options if you want to display it differently.
(this does assume your regional date format is d/mm/yyyy if you have the month and day reversed we may need to talk you through using text to columns.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel 2021 - how to set format for date&time as 'dd.MM.yyyy HH:mm'?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[senglory]

Book1.xlsm
	D	E	F
2
3		02.01.2023 12:00
4
Sheet2

For the Cell, I have created a custom format dd.mm.yy HH:mm and it gives the expected result

How can I specify this date format here?