Cell formatting in Mail Merge

I have an Excel spreadsheet, showing dates when a succession of different 
stages are reached for each client in my conveyancing job. All dates are in 
UK format in Excel. However, when I mail merge in Word using this data 
source, all dates appear in Word as US format, and any empty cells are 
populated with 12:00AM.

Could you please tell me if this is an Excel problem, and what the solution 
may be.
0
12/8/2005 3:00:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
291 Views

Similar Articles

[PageSpeed] 18

Debra Dalgleish posted this to a similar question:

There's an article on the Microsoft web site that might help you:

Answer Box: Numbers don't merge right in Word
   http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx

And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill 
has instructions here:

   http://home.earthlink.net/~wordfaqs/CustomizingWord2002.htm

about half way down the page.

=======
I often cheat and just use another cell:

=text(a2,"mm/dd/yyyy")

and use this text cell in the mail merge.

Lighthouse wrote:
> 
> I have an Excel spreadsheet, showing dates when a succession of different
> stages are reached for each client in my conveyancing job. All dates are in
> UK format in Excel. However, when I mail merge in Word using this data
> source, all dates appear in Word as US format, and any empty cells are
> populated with 12:00AM.
> 
> Could you please tell me if this is an Excel problem, and what the solution
> may be.

-- 

Dave Peterson
0
petersod (12005)
12/8/2005 3:21:18 PM
Thanks for that Dave, it's worked a treat - it changes the US dates to UK 
format. Thank god for that! Unfortunately, the empty cells that were 12:00AM 
are now showing today's date. I just want them to be empty. Is there a way of 
telling it to leave empty cells as empty?

Many thanks for your efforts Dave, I'm half-way there at least!

"Dave Peterson" wrote:

> Debra Dalgleish posted this to a similar question:
> 
> There's an article on the Microsoft web site that might help you:
> 
> Answer Box: Numbers don't merge right in Word
>    http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx
> 
> And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill 
> has instructions here:
> 
>    http://home.earthlink.net/~wordfaqs/CustomizingWord2002.htm
> 
> about half way down the page.
> 
> =======
> I often cheat and just use another cell:
> 
> =text(a2,"mm/dd/yyyy")
> 
> and use this text cell in the mail merge.
> 
> Lighthouse wrote:
> > 
> > I have an Excel spreadsheet, showing dates when a succession of different
> > stages are reached for each client in my conveyancing job. All dates are in
> > UK format in Excel. However, when I mail merge in Word using this data
> > source, all dates appear in Word as US format, and any empty cells are
> > populated with 12:00AM.
> > 
> > Could you please tell me if this is an Excel problem, and what the solution
> > may be.
> 
> -- 
> 
> Dave Peterson
> 
0
12/8/2005 3:39:02 PM
I'd still use that extra cell:

=if(a1="","",text(a1,"mm/dd/yyyy")



Lighthouse wrote:
> 
> Thanks for that Dave, it's worked a treat - it changes the US dates to UK
> format. Thank god for that! Unfortunately, the empty cells that were 12:00AM
> are now showing today's date. I just want them to be empty. Is there a way of
> telling it to leave empty cells as empty?
> 
> Many thanks for your efforts Dave, I'm half-way there at least!
> 
> "Dave Peterson" wrote:
> 
> > Debra Dalgleish posted this to a similar question:
> >
> > There's an article on the Microsoft web site that might help you:
> >
> > Answer Box: Numbers don't merge right in Word
> >    http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx
> >
> > And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill
> > has instructions here:
> >
> >    http://home.earthlink.net/~wordfaqs/CustomizingWord2002.htm
> >
> > about half way down the page.
> >
> > =======
> > I often cheat and just use another cell:
> >
> > =text(a2,"mm/dd/yyyy")
> >
> > and use this text cell in the mail merge.
> >
> > Lighthouse wrote:
> > >
> > > I have an Excel spreadsheet, showing dates when a succession of different
> > > stages are reached for each client in my conveyancing job. All dates are in
> > > UK format in Excel. However, when I mail merge in Word using this data
> > > source, all dates appear in Word as US format, and any empty cells are
> > > populated with 12:00AM.
> > >
> > > Could you please tell me if this is an Excel problem, and what the solution
> > > may be.
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12005)
12/8/2005 5:14:47 PM
Reply:

Similar Artilces:

Conditional Formatting Problem #4
Below is an example spreadsheet that I’m dealing with. I have a column with “Standards” (more on that later), but I have my results in three possible ‘formats’ “Standard” Result 1 Result 2 Result 3 0.01 0.123 0.123J <0.123 1.0 0.123 0.123J <0.123 First, I want to create a conditional format that will BOLD only the cells that have just numbers in them (i.e. 0.123), and leave the other two types alone. Second, I will be comparing the cells with only numbers in them (i.e. Result 1) to the Standard and highlighting only those values that are greater to or equal to the Standard. In...

Can i use conditional formating on a cell when it contains a formula? #2
=mid(text(a1;"0000000000.00");1;1 -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi the reason for this is that thie formula returns a string and not a numeric value. Change the formula to: =--mid(text(a1;"0000000000.00");1;1) -- Regards Frank Kabel Frankfurt, Germany sit wrote: > =mid(text(a1;"0000000000.00");1;1) OR ... Change your Conditional formula to: =&quo...

Recovering deleted mails when retention policy is set
If the "recover deleted mails" option does not show any mails available to recover, how do i restore a users deleted mails? We have a retention policy set, and I'm wondering if the mails are retained somewhere on the exchange server even if they don't show as available for reovery via Outlook? if the emails were "hard deleted", see this: http://support.microsoft.com/kb/246153/en-us if they don't show up there, either, the user has gone into the dumpster and "purged" email...your only option after that is to restore from backup... -- Susan Con...

we have 2 OE email accts
My husband and I both have new Outlook Express email accts. However, it appears the same message is received in both our accts. How can I separate incoming mail to the proper accts? thanks Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://www.insideoutlookexpress.com/ --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the d...

Outlook 2007 Mail fails to Send, stuck in Outbox
I use MSOutlook 2007. Accounts setup correctly etc. I have tested the account and it comes back fine without errors. Everything has been working fine until - I was in My Documents and chose to send a document to 'email recipient' by right clicking on the document. It went right to Outlook and appeared to be sending. It remained in the Outbox and I kept doing a send/receive. All the while it stays in the Outbox and doensn't appear in the Sent Items folder. I discovered it IS sending as the recipient received it several times. I have re-booted etc. still does this. I have dele...

Anchored cell won't release
I have recently encountered a situation with several users (probably coincidental) in which a cell seems to be anchored and won't release. They can close Excel and reopen it and the same cell will be anchored. You can select any other cell in the worksheet and the entire range from the anchored cell and the selected cell will be highlighted. Any ideas on what is causing this and how can I resolve it? Thanks, Mike Hi Press F8 to cancel Extended mode. -- Regards Roger Govier "MikeS" <MikeS@discussions.microsoft.com> wrote in message news:5D1440C0-7CE0-46D0-87F...

Sort by formatting
I have a massive spreadsheet I have used a finddups macro on and have changed the formatting of cells with duplicate data. I would like to be able to sort or filter the data by conditional formatting so all the suspect duplicates can easily be copied to another spreadsheet. Hunt and peck is not practical with over 50,000 rows. Is there any way to sort/filter this data? I am currently using Excel XP and Windows XP. TIA George Hi sorting by formats is not supported by Excel. I'd use a helper column to 'tag' the respective rows and use this helper column for sorting/filtering....

duplicate cells
I have a spreadsheet of Names and addresses. the list is very large (about 8000) there are quite a few duplicate entries that I would like to eliminate. is there a formula or a uncomplicated method that will do this for me? bear in mind that I am a novice with excel spreadsheets.d appreciate any help. Thanks Richard, have a look at Chip's site for a way to do it. http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows You may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm if you need more help post back with...

Excel using =RC[-1] format and I don't want it
On this machine only, Excel will use the format =RC[-1] when I am entering a formula on the command line by hand and click on a cell to reference it in the formula. I don't know how this got on but I would like it to go off. On my other machines, it does the old familiar A1:B2 style of cell reference. I can't even search for how to fix it since I can't determine what this is called so I can turn it off. The only reference I have seen is a button that is only effective for macro creating and I am not creating a macro, just a formula, interactively, in the cell. How ...

Printing Problems (cells printing on top of one another)
When printing cells are merged together like the they have been shifted up half a row height. This problem is restriced to one user on one system (Windows 2000 Pro and Office 2000). The problem is same on a single sheet workbook or a multi sheet workbook. The printer setting keep reverting back to 11 sheet tall by 1 sheet wide. This produces an unclear image through print preview. You can manually reset the default to 1 sheet wide by 1 sheet long which corrects the print preview but not the printed output. I avoid merged cells like the plague - full of problems. I guess that you ...

Subject Line for E-mail Template
I am creating E-mail Templates; I put a subject line in the template and save it. When I insert the template into e-mail to be sent the subject line does not copy over. Why? If I send out e-mail as part of a workflow will I have the same result? Thank you. Hi Ken if you use the INSERT TEMPLATE option in Activity Emails, the Subject Line doesn't copy over, but if you use the workflow, it will. -Mark "Ken Schisler" wrote: > I am creating E-mail Templates; I put a subject line in the template and save > it. When I insert the template into e-mail to be sent the subje...

What is error# 0x80040102, my Domino connector is not reading mail
My calendar & tasks are read by the outlook easily but fails to read mails. It's working for perfect for a collague but fails on me every time. Finally this is the error which is shown by the replicator. ...

Chart formatting #3
Is there a way to shade certain areas of a line graph? I have a simple line graph extending horizontally from point 1 to 10 on the x-axis. I would like to shade the entire background area of the graph from points 2-4 and points 6-8. Is there a way to make the graph apear that the background is grayed out between these points. Thanks Chris Chris - This has been sitting here a long time... Make a combo chart. Start by locking the Y axis scale parameters (uncheck the auto boxes). Add a series to your chart which has zero values where you want no shading and <Y axis max> where ...

E-mail Addresses Tab Not Displaying in AD Users and Computers
Howdy, With Win2k3 & Exchange 2k3 SP2. I have several users that are not displaying the "E-mail Addresses" Tab on the properties of their user object. As well they do not list in the GAL. Any ideas or pointers would be helpful. Thanks, Dan Foxley That would suggest that they are not mail or mailbox enabled. In ADUC right click, Exchange tasks.... Nue <danfoxley@gmail.com> wrote in message news:1146514613.576153.123400@j33g2000cwa.googlegroups.com... > Howdy, > > With Win2k3 & Exchange 2k3 SP2. I have several users that are not > displaying the "...

excel e-mail file
i rcvd an e-mail with an excel doc. attach. i do not have microsoft excel on my computer. is there any other program that will open an excel doc. >-----Original Message----- >i rcvd an e-mail with an excel doc. attach. >i do not have microsoft excel on my computer. >is there any other program that will open an excel doc. >. >never mind, i was able to open it with microsoft access. Hi there is a free Excel viewer available at http://www.microsoft.com/downloads/details.aspx?FamilyID=4EB83149-91DA- 4110-8595-4A960D3E1C7C&displaylang=EN HTH Frank anonymous@discussions.m...

How to make outlook the default mail client
In my outlook express I see that in the options it is indicated that outlook express is the default email client. Then I look in outlook 2003 and I see in its options that it shows it is te default email client. There's a contradiction here, how do I fix that? Any help would be greatly appreciated. Bob Control Panel->Internet Options->Programs Tab. Set your defaults from there. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious he...

Workflow Rules and E-Mail Templates
We'd like to send an e-mail to a specific mailbox every time an account or contact is created that includes the data fields from the object. Is there any way to e-mail the data fields through workflow without using an e-mail template? I'm asking because when you use an e-mail template, the recipient field is disabled in the workflow (which makes no sense why it disables the field). I know I can use a post-URL action, but that's a lot of work to just send an e-mail with the data fields. Thanks, Roy There is an unsupported/undocumented method of adding dynamic data to em...

cant receive mail unless relaunch outlook
exchange 2000 sp3, post sp3 rollup outlook xp or 2003 the problems are only experienced by some users, not all. and it happens with both outlook xp and 2003. problem 1: an internal user will send an email to another internal user. the user will not receive the email even when manually clicking send/receive. if he closes outlook and relaunches it, then he receives the message. problem 2: remote user connect to office via vpn and runs outlook. 5 or 6 messages arrive and no more. waiting and/or hitting send/receive still produces no more messages. closing and relaunching outlook will bring in ...

Merging From OL to Publisher
Hi, I don't know if this a publisher or an OL ?. I want to be able to merge to a category of OL. When I am in a Publisher File that I want to do a mail merge, I go through the wizard which is great, then when I choose the contacts folder, I get the Mail Merge Recipients Window. At that point categories are not an option to filter. Is it possible to add categories as an option to the Mail Merge Recipients Window? The way I do it now is to keep an empty contact folder and copy the category over and then merge that folder. BTW Office '03 & '07 "Rob A"...

Different kind of cell reference.
I have a need to calculate a formula that I don't know how to reference. The following is the pattern that I could hard code. Basically, I have a value for each year, and need to interpolate monthly values. Is there a more generic way of entering these cells? =D1 + (D15-D1)*1 =D1 + (D15-D1)*2 =D1 + (D15-D1)*3 =D1 + (D15-D1)*4 =D1 + (D15-D1)*5 =D1 + (D15-D1)*6 =D1 + (D15-D1)*7 =D1 + (D15-D1)*8 =D1 + (D15-D1)*9 =D1 + (D15-D1)*10 =D1 + (D15-D1)*11 =D1 + (D15-D1)*12 =D30 + (D30-D15)*1 =D30 + (D30-D15)*2 =D30 + (D30-D15)*3 =D30 + (D30-D15)*4 =D30 + (D30-D15)*...

Deleting rows when cell has "#DIV/0!" error
Hi, I'm currently using this code to delete rows with balnk cells. However, i have changes the formatting of my sheet and the cells are no longer blank they have the "#DIV/0!" error. How can I delete the entire row when the cell in column D has that value? Sub DeleteRowsIfDIsBlank() With payrollsht Dim rg As Range, rgBlank As Range Set rg = Cells.Range("D:D") On Error Resume Next Set rgBlank = rg.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rgBlank Is Nothing Then Else rgBlank...

negative number format
I have a report uploaded to excel from a particular program used here at work. In that program, negative numbers have the format of $###.00-, with the minus sign at the end instead of the beginning. When the data comes over to Excel the minus sign is still at the end of the number. The text currently has a General Format with No Specific Number Format. I have been manually moving the minus signs to the beginning in each cell, then changing the Column to Currency Number Format which is very time consuming. Is there a formula I can use in the Cell, Column and/or Worksheet containing...

Formatting Cells Question
Hi I am having troubles with formatting cells in EXCEL 2003. I have a column of many hundreds of rows lthat contain data like this: 01Jan12 on fist glance this looks like a date but it really is not. EXCEL on the other automatically assumes it is a date and formats as such. 2001-Jan-12 I have tried formatting as text, General and even experimented with custom formats to no avail. In the same column I have 03Mar1543 Which is dispolayed exactly as is. How can I get EXCEL to simply display data like 01Jan12 exactly as is? You must format the cell as Text BEFORE typing any information...

copy Outgoing mails via the server
how can i copy all the outgoing mails via the server to another mail box ? click properties on the store object, and there you have an option to fowored all mails to a specific mailbox. "Indika Gamage" <indikag@hotmail.com> wrote in message news:04d501c46e2b$a7c06730$a401280a@phx.gbl... > how can i copy all the outgoing mails via the server to > another mail box ? what u mean by store object? pls explane the steps >-----Original Message----- >click properties on the store object, and there you have an option to >fowored all mails to a specific mailbox. &...

e-mail on a workflow
I have made a customization that makes an e-mail to automatically be sent when you change an attribute. Does anyone have a solution that makes the e-mail to pop-up instead so you manually can edit it and send it!? On Apr 24, 4:50=A0am, Martin <Mar...@discussions.microsoft.com> wrote: > I have made a customization that makes an e-mail to automatically be sent > when you change an attribute. Does anyone have a solution that makes the > e-mail to pop-up instead so you manually can edit it and send it!? I recently wrote a blog entry about something similar; creating a button to ...