Using Concatenate on a date/time field

[Chimelle]

I have data the is in this format 10/1/2015 09:57:49 AM. I need to change it to be 01-OCT-2015 09:57:49 AM. When I try concatenate for the day I get some weird numbers back. Get the same when I try concatenating the time piece. Any help is greatly appreciated.

 

[Joe4]

Welcome to the Board!

The important thing to understand about dates and times is that they are actually stored as numbers in Excel, specifically the number of days since 1/1/1900. So time is really just a fraction of the day.

If your entry is entered as date/time and not text, just reformat it to show the way you want.
The CUSTOM FORMAT would be: dd-mmm-yyyy hh:mm:ss AM/PM
The Excel function would look like: =TEXT(A1,"dd-mmm-yyyy hh:mm:ss AM/PM")
(but note that the TEXT function returns a text string).

 

[Caribeiro77]

you have to use the TEXT function, something like this:

.....&TEXT(A1,"DD-MMM-YYYY hh:mm:ss AM/PM")

 

[Chimelle]

Thank you! I was starting to take a very long way around to get this same result!

 

[Learning To Excel]

=TEXT(A1,"dd-mmm-yyyy hh:mm:ss AM/PM")