Mailing date issue
Titionebe -
Hello,
I have a problem with my dates and my mail merge.
I have a spreadsheet in Excel where I insert dates, which are not necessarily precisely known. So I format them as 01/01/2000 for the better-known ones, Jan-2000 when I only know the month, 2000 when I only know the year, or simply "unknown date" when I don't know it. And so, when I do my mail merge, the cells formatted as dates give me the version retained by Excel (as a number of days since I don't remember when), which annoys me.
To solve this problem, I found the solution to create a second column (which is the one I use for mail merge) in which I put a formula that converts my date into plain text, allowing me to do efficient mail merging.
However, another problem arises, one that puzzles me and that I would like to share with you. I have tested formulas every which way, turning them this way and that, but I can't get what I want: a formula that allows me to translate dates in "dd/mm/yyyy" format into text, but leaves the others as they are.
Here are the last two attempts and their issues:
=IF(ISTEXT(A1),A1,IF(A1>DATE(1950,1,1),TEXT(A1,"dd/mm/yyyy"),A1)) which has the problem that dates like "Jul-19" are converted to "01/07/2019" instead of staying as "Jul-19"
=IF(TEXT(A1,"dd/mm/yyyy")<>A1,TEXT(A1,"dd/mm/yyyy"),A1) which has the problem that dates like "2020" are translated to "12/07/1905" instead of staying as "2020"
In case you're wondering, I'm using version 2412 of Excel.
Thank you in advance for any help you can provide.
3 réponses
Thank you for your help, but I eventually found another solution. I noticed that the dates converted into the number of days were consistently above 10,000 (so 5 digits), and since we are before the year 10,000, the number of digits in the year was less than 5... so I made this formula: =IF(ISTEXT(AB2);AB2;IF(LEN(AB2)>=5;TEXT(AB2,"dd/mm/yyyy");AB2))
It's a workaround, not very elegant, but it works
Hello,
My God, don’t bother with all that, it’s a known issue!!!
Just add a formatting to your field
Replace the merge field {Date_de_naissance} with {Date_de_naissance \@ ''dd/MM/yyyy''}
See you+
Hello, thank you for your quick response!
But this is indeed the first solution I've seen while scouring the forums... and what it gives me is that the dates that are supposed to be expressed with just the year are translated into date dd/mm/yyyy, which I don't want...
My problem comes from the fact that I need to manage multiple formats at the same time. That's why I need a solution with several conditions.
