CONCATENATE Time Cells

[ExcelJunkie]

Hi All:

How do I concatenate two cells that are formatted to TIME, but remove the "AM" and "PM"?

Here's what I have:

Cell A1: 8:00 AM
Cell B1: 4:00 PM

In cell G1, I want to concatenate the two and drop the "AM" and "PM" so that it only shows "8:00 - 4:00"

I'm currently using:

=CONCATENATE(A1," - ",B1)

... yet my output in G1 is: "0.333333333333333 - 0.666666666666667"

I want ... "8:00 - 4:00"

Thank you!

[VBA Noob]

Hi,

Would this work for you

=CONCATENATE(TEXT(A1,"hh:mm")&" - ",TEXT(B1,"hh:mm"))

VBA Noob

[SteveG]

You could try this as long as your start time is always AM and the end time is always PM,

=A1*24&":00"&" - "&((B1*24)-12)&":00"


This would return 8:00 - 4:00. I believe the last post would return the 4:00 PM as 16:00.


HTH

Steve

[ExcelJunkie]

Thank you both so much, but the start time will end in both AM and PM.

I could have:

4:00 PM
12:00 AM

which I would still want "4:00 - 12:00"

So, how would I do it then?

Steve G: You are correct, VBA Noob's code works great but shows "8:00 - 16:00", this may confuse the non-military time reading folks.

Thank you, thank you!

[oldchippy]

Hi All:

How do I concatenate two cells that are formatted to TIME, but remove the "AM" and "PM"?

Here's what I have:

Cell A1: 8:00 AM
Cell B1: 4:00 PM

In cell G1, I want to concatenate the two and drop the "AM" and "PM" so that it only shows "8:00 - 4:00"

I'm currently using:

=CONCATENATE(A1," - ",B1)

... yet my output in G1 is: "0.333333333333333 - 0.666666666666667"

I want ... "8:00 - 4:00"

Thank you!

Hi ExcelJunkie,

Formula in C1

=(A1*24)-12&":00"&" - "&(B1*24)-12&":00"

oldchippy

[ExcelJunkie]

Thank you OldChippy!

All of you rock! Thanks for such fast responses. I really appreciate you folks!

However, your code outputs:

-4:00 - 4:00

[oldchippy]

Thank you OldChippy!

All of you rock! Thanks for such fast responses. I really appreciate you folks!

However, your code outputs:

-4:00 - 4:00

When you enter 8:00 don't forget to add the PM, or it will think it is 4:00 AM

You'll find it will work then

[ExcelJunkie]

Thank you again!

Your code works if the start time is PM. However, I have exactly:

8:00 AM
4:00 PM

it outputs -4:00 - 4:00. I need something that will work regardless of the time of day for start time, whether AM or PM.

[VBA Noob]

How about

=CONCATENATE(TEXT(A1,"hh:mm AM/PM")&" - ",TEXT(B1,"hh:mm AM/PM"))

VBA Noob