CONCATENATE Time Cells

Hi,

Would this work for you

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

VBA Noob

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

Originally Posted by 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!

Hi ExcelJunkie,

Formula in C1

=(A1*24)-12&":00"&" - "&(B1*24)-12&":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

Originally Posted by 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

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

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.

How about

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

VBA Noob

or

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


VBA Noob

TEXT(A1,"hh:mm") will return a zero if the time in A1 is 12:00 AM. the problem is that you have 9 different scenarios possible here. Your start time could be less than 12 AM, equal to 12 AM or greater than 12 AM as could your end times. There certainly could be a better way but if you want you could set up a table with the following formulas. I used this in H1:H9.

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

These account for the 9 different scenarios.

In F1:F9 input the following formulas.

=IF(AND(($A$1*24)<12,($A$1*24)<>0,($B$1*24)<12,($B$1*24)<>0),1,"")
=IF(AND(($A$1*24)<12,($A$1*24)<>0,($B$1*24)=0),2,"")
=IF(AND(($A$1*24)<12,($A$1*24)<>0,($B$1*24)>12),3,"")
=IF(AND(($A$1*24)=0,($B$1*24)<12,($B$1*24)<>0),4,"")
=IF(AND(($A$1*24)=0,($B$1*24)=0),5,"")
=IF(AND(($A$1*24)=0,($B$1*24)>12,($B$1*24)<>0),6,"")
=IF(AND(($A$1*24)>12,($B$1*24)<12,($B$1*24)<>0),7,"")
=IF(AND(($A$1*24)>12,($B$1*24)<12,($B$1*24)<>0),8,"")
=IF(AND(($A$1*24)>12,($B$1*24)>12),9,"")

Then in G1 I entered,

=CHOOSE(SUM(F1:F9),H1,H2,H3,H4,H5,H6,H7,H8,H9)

This will lookup the value in F1:F9 and return the corresponding values from
H1:H9.

HTH

Steve

Surely it's just another Substitute statement. ??

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

VBA Noob

Try...

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

Hope that helps,
John

VBA Noob,

Absolutely! Would have saved me a lot of typing had I not just glanced at your post.


Regards,

Steve

Thank you all again so much!

VBANoob:

Your code is the shortest and works the best!

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

Works perfectly!

You rock! You all rock!

See you next time.