Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: Stop Excel displaying seconds in Mail Merge

    HI all,

    A question I hope will be nice and easy for you!

    I am importing data from an inhouse data programe to Excel. One of the columns is a flight departure time. From this sheet I need to mail merge to a word document which extracts many of the fields including the departure time. However, the time always displays as hh:mm:ss. I only need to show hh:mm. I have tried changing in the format but in the formula bar is still shows hh:mm:ss which is what is being merged.

    Please help!!! this will save us a lot of time in the office as we currently hand write everything! I'm not that savvy on jargon either so please kindly keep it simple for me!!

    Many thanks - Jamester

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howy, and welcome to the board. It may be that Excel is considering as text, not a (serial) number. One way to convert is to type the number 1 in a blank cell, copy it, then select the column with dates, and Paste Special > Multiply. This will convert them to true numbers, and then apply formatting as date as needed.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2006
    Posts
    3
    Hey..!
    Thanks for the advice.. I tried it but alas it doesn't work. It changes a time of 23:30 to 0.98! Can't tell someone they'll be picked up at 0.98hrs!

    Any other ideas?

    Thanks
    J

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    That's because it is now a serial number. 23:30 is the equivalent of the fraction of the day 0.98.

    Also, are you wanting only the 23:30, and not the date with it? If so, then format as Time rather than Date, and choose the hh:mm option.

    And if you have an extra column, you can use this formula to get what you want: Assume that your date is in cell B3, then in C3, put this:

    =HOUR(B3)&":"&TEXT(MINUTE(B3),"00")
    Last edited by shades; 12-16-06 at 09:23.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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