Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-12-2011, 06:04 AM
borntorun75 borntorun75 is offline Mail merge will not format date field generated by Excel IF statement Windows XP Mail merge will not format date field generated by Excel IF statement Office 2007
Novice
Mail merge will not format date field generated by Excel IF statement
 
Join Date: Dec 2011
Posts: 2
borntorun75 is on a distinguished road
Default Mail merge will not format date field generated by Excel IF statement


Hi there,

I am using Excel (2007) as a data source and picking up a date column which can either be populated or not. The date is an 'end' date to a product. If the cell is populated, that means the product will discontinue to be available from that date. If the cell is empty, the product will be available indefinitely.

The cell is populated by an IF statement in Excel, the result being that the cell can have a date in it or not (blank).

The cell is definitely behaving as a date field. i.e. it is an underlying numerical value, which is formatted as a date.

As stated though, the cell in some circumstances may be blank - since there is no product end date.

It is also formatted, for what it's worth, as a date cell in Excel.

However, when attempting to mail merge that field, MS Word will not take any account of the mail merge merge formatting switches and defaults to showing it in the US format MMMM dd yyyy. It is not taking any account of my switch which is {MERGEFIELD End_Date \@ "dd MMMM yyyy"}

If I use a non-calculated date field (i.e. a typed in value), the switch behaves as expected, and shows it in the UK format.

The problem is only manifesting itself on that cell which is the consequence of an IF statement.

I'm very familiar with the usual date formatting issues in Word, but I cannot find an answer to this one.

Any takers ?

best regards, borntorun75.
Reply With Quote
  #2  
Old 12-14-2011, 02:02 PM
macropod's Avatar
macropod macropod is offline Mail merge will not format date field generated by Excel IF statement Windows 7 64bit Mail merge will not format date field generated by Excel IF statement Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi borntorun75,

Care to post a copy of the Excel wb with enough data to demonstrate the issue?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-16-2011, 04:51 AM
borntorun75 borntorun75 is offline Mail merge will not format date field generated by Excel IF statement Windows XP Mail merge will not format date field generated by Excel IF statement Office 2007
Novice
Mail merge will not format date field generated by Excel IF statement
 
Join Date: Dec 2011
Posts: 2
borntorun75 is on a distinguished road
Default

Hi there Macropod,

I have attached a sample Word document (.docx) and a corresponding sample of data (.xlsx). The document and data is factitious, but representative of my problem.

Referring back to my original post, I think I was misleading though in what I said.

I posted ... "However, when attempting to mail merge that field, MS Word will not take any account of the mail merge merge formatting switches and defaults to showing it in the US format MMMM dd yyyy. It is not taking any account of my switch which is {MERGEFIELD End_Date \@ "dd MMMM yyyy"}".

In actual fact, the above is not what I should have said. Nothing of the like - it is actually all to do with how MS Word is interpreting the dates - not how it shows/formats the dates.

What is actually happening is that MS Word is showing the date is "dd MMMM yyyy" as I want it to. However, it is interpreting the date in some instances as being US format, and not UK format. That is the nub of my problem. I am sorry and I've no idea why I wrote the problem down as I did originally. It made sense to me at the time (!).

It is very evident that the dates it is having problems with are those where the dd and mm are both less than the number 12. If the date has 13 or higher in the first 2 digits (e.g. 13/02/2011), then of course MS Word *has* to interpret it as 13th February.

However, if there's a date of 02/05/2011, then instead of determining 2nd May 2011, it is concluding that it's 5th February 2011 instead. It is doing that reasoning on a record-by-record basis and not continuing the formatting based on what it has processed previously.

If you look at the attached spreadsheet, you can see the last two columns are ones I've put in. They show what I would want to show in the mail merge, compared to what it does show.

Hope the above makes sense and apologies for giving you a false impression of the problem.

In the end, to get around the problem, within Excel, in my Date_Ending column I, changed its formula...

From : =IF(D2="","",IF(AND(E2<>"",E2<D2),E2,""))
To : =IF(D2="","",IF(AND(E2<>"",E2<D2),TEXT(E2,"dd MMMM yyyy"),""))

That results of that formula then correctly got passed into the mail merge. However, I'd be curious if I could get around the problem within MS Word.

Thanks if you can help.
Attached Files
File Type: docx Sample_Document.docx (11.2 KB, 12 views)
File Type: xlsx Sample_Data.xlsx (9.7 KB, 16 views)
Reply With Quote
  #4  
Old 12-16-2011, 06:28 AM
macropod's Avatar
macropod macropod is offline Mail merge will not format date field generated by Excel IF statement Windows 7 64bit Mail merge will not format date field generated by Excel IF statement Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi borntorun75,

I suspect the fault has something to do with the empty cell in F2. As a workaround, you could change the formatting in column F to 'dd-mmm-yyyy'. Alternatively, you might try changing the data connection method to DDE.

Also, FWIW, you could also replace the 'Date_Ending' IF test in your mailmerge main document with a mergefield coded as: {MERGEFIELD Date_Ending \@ "dd MMMM yyyy" \b "Item available until "}
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Import field from Excel to Powerpoint xgravity23 Office 8 11-22-2012 02:08 PM
Mail merge will not format date field generated by Excel IF statement Use a mail merge Field as the final doc filename DaveBF Mail Merge 1 10-22-2011 10:16 PM
Mail merge will not format date field generated by Excel IF statement HTML Content in Mail Merge Field Ilmater Mail Merge 1 09-04-2011 02:47 AM
Mail merge will not format date field generated by Excel IF statement Mail merge erases field codes Medievalguy88 Mail Merge 1 08-11-2011 05:21 AM
Mail merge will not format date field generated by Excel IF statement Date not transfering to mail merge correctly tonywatsonmail Mail Merge 1 07-01-2011 05:30 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft