Register
Log In
Unanswered
Home
New Member
August 26, 2017
Offline
Hi. First time posting.
I am using the following formula to concatenate a series of dates:
=IF($D$5="","","The expiration date(s) for all active(s) is(are): "&SUBSTITUTE(SUBSTITUTE(TRIM($D$9&" "&$U$9&" "&$AL$9&" "&$BC$9&" "&$BU$9&" "&$CL$9&" "&$DC$9&" "&$DT$9&" "&$EL$9&" "&$FC$9&" "&$FT$9&" "&$GK$9&" "&$HC$9&" "&$HT$9&" "&$JM$9&" "&$JB$9)," ","; "),";;","; "))
The result is:
|
The expiration date(s) for all active(s) is(are): 43890; 43900; 43353
When I use Number/Format/Date and choose a date format, nothing happens. When I did that on the individual cells in the formula, the serial numbers converted to the date format I wanted. I converted the original data to the same date format and it still didn't work. I even tried the formula without the initial text string and no change in the result. Any suggestions? Thank you very much, Marty
|
VIP
Members
Dashboards
Power Query
Power Pivot
Power BI
December 7, 2016
Offline
Hi Marty,
When you combine text and numbers in one cell like you do here Excel treats it as text, even the numbers.
Below is an example of how you can do to fix this. You can check the attached file for more details.
What you need to do is in a new row (or sheet for that matter) fetch the values from cells D9 to JB9 and format as text using TEXT function. In below example I use row 11, cells D11 to JB11. That formula would then be =TEXT(TRIM(D$6),"yyyy-mm-dd") to fetch the date serial number from cell D9 and convert it to text formatted as date.
Your new formula to show the result will be much shorter and instead of SUBSTITUTE you use TEXTJOIN. The new formula would then be =IF($D$5="","","The expiration date(s) for all active(s) is(are): "&TEXTJOIN("; ",TRUE,D11:JB11)).
Check out the attached file and you will see how it works.
Br,
Anders
New Member
August 26, 2017
Offline
Hi Anders. Thank you very much for your help. Unfortunately, TEXTJOIN is not an option in my version of Excel (2010). When I started to type in TEXTJOIN, there were no other functions listed following TEXT. Looks like I may still have to use SUBSTITUTE for the dates I converted to text following your suggestion.
Thanks once again,
Marty
VIP
Members
Dashboards
Power Query
Power Pivot
Power BI
December 7, 2016
Offline
Hi Marty,
True, TEXTJOIN came with Excel 2016 version. I hope you get it to work as you want it to be.
Br,
Anders
Most Users Ever Online: 107
Currently Online: Matthias, Mirjam Pecnik
23 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 995
Frans Visser: 267
Anders Sehlstedt: 176
David_Ng: 133
A.Maurizio: 88
Anne Walsh: 76
mey tithveasna: 71
MITUL PARIKH: 68
julian: 61
Mohamed Saliha: 59
Newest Members:
John Van Jo
Rick Kamps
Sunil Rattewal
Jasmine Khoo
Kurstin King
Mhamed Deghaies
John zschoche
Rod Hess
nikhil joshi
Pradeep Bhatt
Forum Stats:
Groups: 2
Forums: 21
Topics: 1477
Posts: 6761
Member Stats:
Guest Posters: 2
Members: 47029
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
