Excel Date Format and Word Merge Problem

  Taff36 17:25 02 Jul 05
Locked

I have a members database supplied as a query of our web based database. In three columns I have dates - date joined, date of birth and record updated. In order to inform my fellow members of the records we hold and allow them to amend them I have merged a letter into word.

If the cell is blank it appears as 12:00:00AM. Some of the dates that do appear for example: 11/22/2004 which should of course be 22/11/2004. In one of the fields it returns 1/18/2005 3:15:54AM (Our Administration Officer was either working late or getting up early) Note the Excel field in this example just shows date not time)

I have tried formatting the columns differently but I haven`t found a combination that works. Help please!

  VoG II 17:44 02 Jul 05

I've just tried this with some random dates typed into a worksheet and it appears to work perfectly!

My dates are formatted as Date - Type 14/03/2001.

They appear correctly in the merged Word document; blanks appear as blanks.

So I'm a bit baffled.

Office 2003 by the way.

  Taff36 18:15 02 Jul 05

Thought you were around so grateful of your help again. Been some time since I last needed it but that`s because I don`t use Excel too much!

Word & Excel 2002. I can`t readily tell which format the cells/columns are in nut two of them show 20/06/2005 and one 20/06/2005 12:55:10 in the box at the top of Excel although they all just show 20/06/2005 in the actual cell. That`s a clue I think.

I`m thinking along the lines of inserting an adjacent column, formatting it correctly and making these destination cells equal to the originals. Then maybe copying and paste special (Values) to sort them out. What do you think? I`ve only got the theory - might need handholding on execution though!

  VoG II 18:25 02 Jul 05

It sounds as if although the dates are appearing as dates they may be dates + times.

Try this. Say your first date is in A2. Some columns to the right enter the formula:

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

Then copy it across and down as far as needed. That will create a set of cells containing just dates. You can then overwrite the original dates by copying the 'new' ones, selecting the 'old' ones, Edit | Paste Special and tick Values.

  Taff36 18:41 02 Jul 05

Thanks for that. The formula in adjacent column produced identical dates (Good) and 00:01:1900 for blanks which means something. Pasting them back as we thought produced the right dates and a zero for the blanks. I think can write a Word field in the merge document that says if this field equals 0 print "We need your date of birth" or even nothing so I`ll try that and let you know after our evening meal. (Suitably washed down with some cholesterol solvent of course) Thanks Vog - back to you later.

  VoG II 18:49 02 Jul 05

Modified formula to avoid the blank cell problem

=IF(A2<>"",DATE(YEAR(A2),MONTH(A2),DAY(A2)),"")

  Taff36 19:02 02 Jul 05

Thanks again. Will try that next.

The first solution worked apart from the zero which has eliminated the time appearing problem. When I tried it on the other two columns however the paste value returned 00/01/1900 not zero. Getting close though.

  Taff36 20:46 02 Jul 05

The modified formula returned zero again I`m afraid.

  Taff36 22:22 02 Jul 05

click here Too late to try this out tonight so will do "catch up" tomorrow.

  VoG II 22:57 02 Jul 05

Sorry, the modified formula should return nothing if the cell is blank, i.e. not 00/01/1900.

  Taff36 23:10 02 Jul 05

It`s got to be the formatting or something with the last link I posted. There is nothing visible in these cells!

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…