combine text with a time function

Question

I need a worksheet function to combine a cell with text and a cell with a time into one cell eg:

I have in C1 Doom ( format = General )

I have in C2 2:38 pm ( format = Custom / h:mm AM/PM )

What I need is in C2 Doom 2:38pm

I use : Windows 10

     : Office 13

can some one help please ?

bill

Answer 1

Hi Rich

What sort of cell format do I use ,if any in your example above?

bill

Your original Column C would be formatted for time--any time format could be used. The whole column or range can be formatted that way even though you are entering text in some of the cells. That's ok. The column with the formulas is just formatted general. The time format for this entry is contained in the formula and the formula result is actually text since it is a concatenate formula.

Rich~M

Answer 2

Then the formula provided by our colleague is the one to use

=C1&" "&TEXT(C2,"H:MM AM/PM")

Answer 3

Hi Bill. I am an Excel user like you.

I have a question regarding your question. You say you have the time in C2, but you want "Doom 2:38 PM" in C2. Where is the time coming from? It either needs to come from another cell or to be inserted as a current time. If you are taking the time from an entry in C2 and entering the result in another cell like D1 you can use this:

=C1&" "&TEXT(C2,"H:MM AM/PM")

If you need to enter a current time at the moment that the data is entered you can use this:

=C1&" "&TEXT(NOW(),"H:MM AM/PM")

This will update the time anytime the formula is updated/re-entered, but since it is entered as text it will not update any other time.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 4

Hi Rich

Thank you it works great and thank you for explaining the formula , I have learned a lot.

bill