Formatting Time Field from Excel Data

[Copwriter]

I am trying to merge a simple form letter with data from an Excel workbook.
Some of the data fields are the results of time calculations in hh:mm:ss
format, and most of them are larger than 24:00:00 (e.g. 160:00:00). When
these fields are imported into Word, they revert to date and time format,
e.g. 201:30:00 becomes 1/8/1900 9:30:00 AM. Is there a way to keep the
fields in strictly time format, as they appear in Excel?

 

[Doug Robbins]

I believe that to get that result in Word, you will need to add some columns
to the datasource to contain the hours, minutes and seconds as separate
values and insert the three mergefields with a colon between each of them.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Copwriter]

I believe that to get that result in Word, you will need to add some columns
to the datasource to contain the hours, minutes and seconds as separate
values and insert the three mergefields with a colon between each of them.

Thanks for the reply, but that solution is too complex for the application I
am working on. I am writing this for non-expert users, and adding that
additional layer of formatting is going to cause them to go sideways. I may
just have to try a calculation that converts the information to a decimal
number. Thanks again, in any event.

 

[macropod]

Hi Copwriter,

You should be able to achieve this if you modify your mergefield.

If you select the errant field and press Shift-F9, it should look something
like:
{MERGEFIELD ExcelTime}
All you should need to do to get this to work the way you want it to is to
add a time switch, thus:
{MERGEFIELD ExcelTime \@ "HH:mm:ss"}
using uppercase 'HH' for the hours, and lowercase 'mm' for the minutes.

If that doesn't work, a more complex field code certainly will:
{QUOTE{MERGEFIELD ExcelTime}{SET ss{=ExcelTime*86400}}{SET
hr{=INT(ss/3600)}}{SET mn{=INT((ss-hr*3600)/60)}}{SET
sec{=MOD(ss,60)}}{=hr*10^4+mn*10^2+sec \# "00':'00':'00"} \@ "HH:mm:ss"}

Cheers

 

[Copwriter]

Macropod: Thanks for the effort. Option #1 rendered the same result as no
switch at all, and I couldn't even begin to understand what #2 does or did.
I'm writing an article on mail merge for beginners, and I think I'd lose my
audience if I was to use your suggestion of very complex code.
Nevertheless, I appreciate the obvious effort and thought you put into your
reply. I did try your suggestion and got an error message indicating that
there were too many picture switches defined.

 

[Doug Robbins]

Well I question whether that topic belongs in an article on mailmerge for
beginners other than to be used as an example of how in the case of some
issues, it is necessary to break the data down into simply chunks that can
be understood by the system.

The date/time formats that a computer uses are based on a 12 or 24 hour
clock and as a result, cannot handle (in the case of a 24 hour clock a
string bigger than 23:59:59 If you want to be able to hande a number of
hours, greater than 23, you CANNOT make used of the date/time formats to
format the result for you. As a result, the simplest thing to do is add the
columns to the worksheet as a I suggested because it is quite simple for
Excel to populate those columns with the relevant parts of the result of the
calculation that you said you were starting with. Then it is not really any
more difficult to insert three mergefields in the Word document than it is
to insert one mergefield.

Using this an example as a method of demonstrating to your audience the need
to respect what it is reasonable to expect the software to be able to do is
probably well worth while. Far better that they should understand a little
bit of what is going on.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP