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

senglory

New Member
Joined
Aug 26, 2008
Messages
7
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Book1.xlsm
DEF
2
302.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
 
Upvote 0
Maybe all you need to do is multiply by 1 all the data using PasteSpecial.
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0
Book1.xlsm
DEF
2
302.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
1682957965380.png


How can I specify this date format here?
 
Upvote 0

Forum statistics

Threads
1,223,991
Messages
6,175,820
Members
452,672
Latest member
missbanana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top