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
|
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
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: 57
Currently Online: Catalin Bombea
5 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Mohamed Rehan Memon
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
