Concatenate/Date array/Min Max Issues

kong1802

New Member
Joined
Feb 7, 2017
Messages
24
I dump a large amount of data with different service dates in each column. The dates are pasted as text. In the next columns, I convert the text to dates. I then have a column to find the min and another column to find the max. There can be up to six service dates in my data. I'm trying to put the min and max together in format mm/dd/yy from min to max. The problem I have is that when the dates are not sequential, it will not concatenate correctly. Below is what one line looks like. Is there a Macro that can be written to put the columns into sequential order and date format first? Or does the concatenate formula just need to be tweeked? Thanks!
1st date

<colgroup><col width="64"></colgroup><tbody>
</tbody>
2nd date

<colgroup><col width="64"></colgroup><tbody>
</tbody>
3rd date

<colgroup><col width="64"></colgroup><tbody>
</tbody>
4th date

<colgroup><col width="64"></colgroup><tbody>
</tbody>
5th date

<colgroup><col width="64"></colgroup><tbody>
</tbody>
6th date

<colgroup><col width="64"></colgroup><tbody>
</tbody>
Column to convert text to date

<colgroup><col span="6" width="64"></colgroup><tbody>
</tbody>
Min Form

<colgroup><col width="64"></colgroup><tbody>
</tbody>
Max form

<colgroup><col width="71"></colgroup><tbody>
</tbody>
Concatenate Formula with issues

<colgroup><col width="216"></colgroup><tbody>
</tbody>
16715

<colgroup><col width="64"></colgroup><tbody>
</tbody>
10272016

<colgroup><col width="64"></colgroup><tbody>
</tbody>
1042017

<colgroup><col width="64"></colgroup><tbody>
</tbody>
010317

<colgroup><col width="64"></colgroup><tbody>
</tbody>
010317

<colgroup><col width="64"></colgroup><tbody>
</tbody>
=MIN(I4:N4)
=MAX(I4:N4)
=IF(O4=P4,CONCATENATE(C4,C4),IF(K4="",CONCATENATE(C4,D4),IF(L4="",CONCATENATE(C4,E4),IF(M4="",CONCATENATE(C4,F4),IF(N4="",CONCATENATE(C4,G4),CONCATENATE(C4,H4))))))

<tbody>
</tbody>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,419
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not sure I understand what you want, but if it's to concatenate the min and max dates how about:

=CONCATENATE(MIN(I4:N4),MAX(I4:N4))
 

kong1802

New Member
Joined
Feb 7, 2017
Messages
24
The problem with that formula is that it shows "00" as its output.

I'm wanting the formula to show "010317010417" as its output.
 

kong1802

New Member
Joined
Feb 7, 2017
Messages
24
I dump a large amount of data with different service dates in each column. The dates are pasted as text. In the next columns, I convert the text to dates. I then have a column to find the min and another column to find the max. There can be up to six service dates in my data. I'm trying to put the min and max together in format mm/dd/yy from min to max. The problem I have is that when the dates are not sequential, it will not concatenate correctly. Below is what one line looks like. Is there a Macro that can be written to put the columns into sequential order and date format first? Or does the concatenate formula just need to be tweeked? Thanks!
1st date

<tbody>
</tbody>
2nd date

<tbody>
</tbody>
3rd date

<tbody>
</tbody>
4th date

<tbody>
</tbody>
5th date

<tbody>
</tbody>
6th date

<tbody>
</tbody>
Column to convert text to date

<tbody>
</tbody>
Min Form

<tbody>
</tbody>
Max form

<tbody>
</tbody>
Concatenate Formula with issues

<tbody>
</tbody>
16715

<tbody>
</tbody>
10272016

<tbody>
</tbody>
1042017

<tbody>
</tbody>
010317

<tbody>
</tbody>
010317

<tbody>
</tbody>
=MIN(I4:N4)=MAX(I4:N4)=IF(O4=P4,CONCATENATE(C4,C4),IF(K4="",CONCATENATE(C4,D4),IF(L4="",CONCATENATE(C4,E4),IF(M4="",CONCATENATE(C4,F4),IF(N4="",CONCATENATE(C4,G4),CONCATENATE(C4,H4))))))

<tbody>
</tbody>


The first date should be showing 01042017, sorry!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,419
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The problem with that formula is that it shows "00" as its output.

I'm wanting the formula to show "010317010417" as its output.
Can you post a sample of 6 dates (as converted from text) and what you want the final result to look like for those dates?
 

kong1802

New Member
Joined
Feb 7, 2017
Messages
24
I figured out a quick fix. I just highlighted the array of text and converted them to numbers. From there I ran the Min and Max in separate columns. Then I ran =Concatenate("0",I7,"0",J7) and it pulled correctly, even when out of sequence.

Is there a VBA/Macro for putting each column in number format?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top