Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Apr 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    time mailmerge (2003)

    Hello all.. I have a mailmerge, the field is in Excel is time. I would like to strip off the am/pm. I have tried the @h:m and it will strip the first 2 records but the 3 rd record and all to the end 66 records all show 1/1/1900 4:10:00 AM. the time is correct but I don't need the date nor the am/pm.

    Thank you for your time.

    Joe

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time mailmerge (2003)

    Welcome to Woody's Lounge!

    Are you sure that the values in Excel are all "real" time values, and not text values looking like time values?

  3. #3
    New Lounger
    Join Date
    Apr 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time mailmerge (2003)

    The fields in excel are all formated as hh:mm am/pm. all but 1 of the vales was arrived by.. = T2 + Time(0,N2,0).. where T2 is a start time and N2 is a number of minutes added. The 1 value was maually entered.

    Thank you for your time.

    Joe.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time mailmerge (2003)

    Could you create a copy of the data source spreadsheet with only the time column, and attach that to a reply? That would enable Loungers to investigate the problem directly.

  5. #5
    New Lounger
    Join Date
    Apr 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time mailmerge (2003)

    Here is a copy.. the dates show up, but I can't seem to find out where to they are coming from.

    Thanks Again.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: time mailmerge (2003)

    Rows C and D have been formatted to show just the time, but you have entered dates as well as times into these cells (except for the first few rows).

    For example Cell D7 appears as 2:09 PM, but if you click in this cell to edit it you will see that it contains 02/01/1900 14:09:00

    StuartR

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time mailmerge (2003)

    1) The value in cell C5 is not really a time value, but a text value. If you select this cell, you'll see that there are lots of spaces before the time value.

    2) The default method for connecting to the data source in Word 2002 and later is OLE DB. This method doesn't work very well. Do the following:
    - Select Tools | Options... in Word.
    - Activate the General tab.
    - Tick the check box "Confirm conversion at Open".
    - Click OK.
    - Activate the Mail Merge task pane, and go back to step 3.
    - Click 'Select a different list'.
    - Browse to your Excel workbook again, select it and click Open.
    - You'll be prompted to select a method to connect to the data source.
    - Select either "Microsoft Excel Worksheet via Converter" or "MS Excel Worksheets via DDE"
    - Do *not* select "OLE DB Data Files" or "Excel Files via ODBC".
    - Click OK, then proceed as usual.

    3) For a 12 hour time format without AM/PM, use @ "h:mm". For a 24 hour time format, use @ "H:mm" (with capital H).

  8. #8
    New Lounger
    Join Date
    Apr 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time mailmerge (2003)

    thank you..but how do you switch between data and formulas?

    Thanks for your time

    Joe

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time mailmerge (2003)

    In Excel? There's a check box for Formulas in the View tab of Tools | Options...
    Or do you mean switching between viewing the merge fields and their results in a merge document in Word? There is a button on the Mail Merge toolbar that toggles this (see below).
    Attached Images Attached Images
    • File Type: bmp x.bmp (434 Bytes, 1 views)

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: time mailmerge (2003)

    Hi JoeB,

    Here's an easy way to do what you want, and it doesn't involve changing any data or data link formats:
    1. Select your mailmerge field in Word
    2. Press Shift-F9. You should now see something like: '{MERGEFIELD "Start Time"}'
    3. Add '@ H:mm' to the field, thus: '{MERGEFIELD "Start Time" @ H:mm}' and delete anything else after "Start Time"
    4. Run your mailmerge
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time mailmerge (2003)

    Simply adding the @ "H:mm" switch does *not* work for me with the data file JoeB provided connected by OLE DB...

  12. #12
    New Lounger
    Join Date
    Apr 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time mailmerge (2003)

    Thank you for the help.. still don't have an answer why the date is showing in a time field. I have looked in the rest of the tabs and it shows up hit and miss.

    Thanks Again
    Joeb

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: time mailmerge (2003)

    Sorry, Hans, you're right - it doesn't work in this case.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: time mailmerge (2003)

    Hi Joe,

    The reason is that Excel stores times as part of a date & time value, where the date is a serial number, expressed as an integer (0 = 31/12/1899), and the time is the decimal portion (1 second = 1/86400th of a day). What you see on screen is just how that value is formatted.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •