how to join these columns

[john5599]

Hi, can someone help me how to join these A,B,C D columns.


Sheet6[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[TD]am[/TD]
[TD="align: right"]10:15:10 AM[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007






Thanks

 

[njimack]

=TIME(A1+IF(D1="pm",12,0),B1,C1)

 

[john5599]

Thank you so much.

 

[john5599]

Thanks

 

[john5599]

Good Day

There is some error showing when I enter 12:30 am it show 12:30 pm as showing in the file below [TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]Time Calculation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]08/06/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]am[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]9:30 AM[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]12/06/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]p[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"]12:30 PM[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: center"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9999FF]#9999FF[/URL] , align: right"]99.00[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

Cell	Formula
F2	=TIME(B2+IF(E2="pm",12,0),C2,D2)
F3	=TIME(B3+IF(E3="pm",12,0),C3,D3)
F4	=((A3+F3)-(A2+F2))*24

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

I rarely use the TIME function and don't fully understand what it's doing when the hour is 12. The below workaround should work

Excel Workbook
	B	C	D	E	F
2	1	30	0		1:30 AM
3	2	30	0		2:30 AM
4	3	30	0		3:30 AM
5	4	30	0		4:30 AM
6	5	30	0		5:30 AM
7	6	30	0		6:30 AM
8	7	30	0		7:30 AM
9	8	30	0		8:30 AM
10	9	30	0		9:30 AM
11	10	30	0		10:30 AM
12	11	30	0		11:30 AM
13	12	30	0		12:30 AM
14	1	30	0	pm	1:30 PM
15	2	30	0	pm	2:30 PM
16	3	30	0	pm	3:30 PM
17	4	30	0	pm	4:30 PM
18	5	30	0	pm	5:30 PM
19	6	30	0	pm	6:30 PM
20	7	30	0	pm	7:30 PM
21	8	30	0	pm	8:30 PM
22	9	30	0	pm	9:30 PM
23	10	30	0	pm	10:30 PM
24	11	30	0	pm	11:30 PM
25	12	30	0	pm	12:30 PM
Sheet1

 

[Finalfight40]

Wouldnt it be because instead of typing 12 30 am you would need to type 00 30 am

Or use a mod formula to change the value of the B column so:

=TIME(mod(B3, 12)+IF(E3="pm",12,0),C3,D3)

 

[john5599]

I rarely use the TIME function and don't fully understand what it's doing when the hour is 12. The below workaround should work
Sheet1

	B	C	D	E	F
pm
pm
pm
pm
pm
pm
pm
pm
pm
pm
pm
pm

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]2:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]3:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]4:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]5:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]7:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]8:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]9:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]10:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]11:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]12:30 AM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]2:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]3:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]4:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]5:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]7:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]8:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]9:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]10:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]11:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]25[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]12:30 PM[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F2 =TIME(B2+IF(E2="pm",12,0)-IF(B2=12,12,0),C2,D2) F3 =TIME(B3+IF(E3="pm",12,0)-IF(B3=12,12,0),C3,D3) F4 =TIME(B4+IF(E4="pm",12,0)-IF(B4=12,12,0),C4,D4) F5 =TIME(B5+IF(E5="pm",12,0)-IF(B5=12,12,0),C5,D5) F6 =TIME(B6+IF(E6="pm",12,0)-IF(B6=12,12,0),C6,D6) F7 =TIME(B7+IF(E7="pm",12,0)-IF(B7=12,12,0),C7,D7) F8 =TIME(B8+IF(E8="pm",12,0)-IF(B8=12,12,0),C8,D8) F9 =TIME(B9+IF(E9="pm",12,0)-IF(B9=12,12,0),C9,D9) F10 =TIME(B10+IF(E10="pm",12,0)-IF(B10=12,12,0),C10,D10) F11 =TIME(B11+IF(E11="pm",12,0)-IF(B11=12,12,0),C11,D11) F12 =TIME(B12+IF(E12="pm",12,0)-IF(B12=12,12,0),C12,D12) F13 =TIME(B13+IF(E13="pm",12,0)-IF(B13=12,12,0),C13,D13) F14 =TIME(B14+IF(E14="pm",12,0)-IF(B14=12,12,0),C14,D14) F15 =TIME(B15+IF(E15="pm",12,0)-IF(B15=12,12,0),C15,D15) F16 =TIME(B16+IF(E16="pm",12,0)-IF(B16=12,12,0),C16,D16) F17 =TIME(B17+IF(E17="pm",12,0)-IF(B17=12,12,0),C17,D17) F18 =TIME(B18+IF(E18="pm",12,0)-IF(B18=12,12,0),C18,D18) F19 =TIME(B19+IF(E19="pm",12,0)-IF(B19=12,12,0),C19,D19) F20 =TIME(B20+IF(E20="pm",12,0)-IF(B20=12,12,0),C20,D20) F21 =TIME(B21+IF(E21="pm",12,0)-IF(B21=12,12,0),C21,D21) F22 =TIME(B22+IF(E22="pm",12,0)-IF(B22=12,12,0),C22,D22) F23 =TIME(B23+IF(E23="pm",12,0)-IF(B23=12,12,0),C23,D23) F24 =TIME(B24+IF(E24="pm",12,0)-IF(B24=12,12,0),C24,D24) F25 =TIME(B25+IF(E25="pm",12,0)-IF(B25=12,12,0),C25,D25) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks