What date format is suitable for 2023.12.20 2:16

[yabi100]

I have an excel file where date and time is shown as follows:
20 Dec 2023 2:16:27
I want them to be shown in following format:
2023.12.20 2:16
How should I define the desired format?

 

[6StringJazzer]

Custom format
yyyy.mm.dd h:mm

 

[yabi100]

Custom format
yyyy.mm.dd h:mm

Thanks for your prompt reply. I already left the office and dont have access to my computer. Let me check your reply, and report tomorrow.

 

[6StringJazzer]

Good luck. You posted the question just before you were leaving the office?

 

[yabi100]

Custom format
yyyy.mm.dd h:mm

Dear Jeff
My problem is that these dates are imported from another program so actually they are text not date. I am able to change them to date using datevalue function. Later when I want to concatnate the date with hour, date is shown as serial number. How can I correct it to be shown as date not a serial number?

 

[yabi100]

I find following reply through search in mrexcel site and solved my problem

Excel Formula:

=A2&" - "&text(B2,"mm/dd/yyyy")&" - "&C2&", "&D2

Thanks fluff for above solution.

 

[6StringJazzer]

The formula you showed gives a result that is nothing at all like the result you said you wanted in your first post. In addition, it draws data from four different cells, also nothing like what you described.

In addition, this solution will still give you text, not a date value.

I don't see how Fluff's formula solves your particular problem.

 

[yabi100]

The formula you showed gives a result that is nothing at all like the result you said you wanted in your first post. In addition, it draws data from four different cells, also nothing like what you described.

In addition, this solution will still give you text, not a date value.

I don't see how Fluff's formula solves your particular problem.

I might not be precise in discribing what I wanted, fluff's solution solved my problem. You are correct that final result is not date but a text. This is ok. Regarding concatnation, that is what I wanted. Anyway thanks for your reply and the time you put into replying my question.