Concatenate/Date array/Min Max Issues

[kong1802]

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>

 

[JoeMo]

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]

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]

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]

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]

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?