Concatenate/Date array/Min Max Issues

K

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!
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]1st date
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]2nd date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]3rd date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]4th date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]5th date
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]6th date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 384"]
<colgroup><col span="6" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 384, colspan: 6"]Column to convert text to date
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]Min Form[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col width="71"></colgroup><tbody>[TR]
[TD="width: 71"]Max form[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 216"]
<colgroup><col width="216"></colgroup><tbody>[TR]
[TD="width: 216"]Concatenate Formula with issues[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]16715[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]10272016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1042017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]010317[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]010317[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=MIN(I4:N4)
[/TD]
[TD]=MAX(I4:N4)
[/TD]
[TD]=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))))))
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
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))
 
Upvote 0
The problem with that formula is that it shows "00" as its output.

I'm wanting the formula to show "010317010417" as its output.
 
Upvote 0
kong1802 said:
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!
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]1st date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2nd date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]3rd date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]4th date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5th date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]6th date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 384, colspan: 6"]Column to convert text to date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Min Form[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<tbody>[TR]
[TD="width: 71"]Max form[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 216"]
<tbody>[TR]
[TD="width: 216"]Concatenate Formula with issues[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]16715[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]10272016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1042017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]010317[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]010317[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=MIN(I4:N4)[/TD]
[TD]=MAX(I4:N4)[/TD]
[TD]=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))))))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


The first date should be showing 01042017, sorry!
 
Upvote 0
kong1802 said:
The problem with that formula is that it shows "00" as its output.

I'm wanting the formula to show "010317010417" as its output.
Click to expand...
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?
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
M
  • Question Question
Need to add _AfterUpdate events to a dynamically-created class
Replies
1
Views
1K
Matrovsky
M
MrDB4Excel
  • Question Question
List of Dates with Duplicate Values
Replies
6
Views
2K
MrDB4Excel
MrDB4Excel
Johnny Thunder
  • Question Question
Formula Help - Converting Date String into an Actual Date
Replies
3
Views
968
Scott Huish
Scott Huish

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

Share this page

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
Back
Top