If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formatting Time Field from Excel Data
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? |
#2
|
|||
|
|||
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" wrote in message ... 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? |
#3
|
|||
|
|||
"Doug Robbins" wrote in message ... 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. |
#4
|
|||
|
|||
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" wrote in message ... 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? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.736 / Virus Database: 490 - Release Date: 9/08/2004 |
#5
|
|||
|
|||
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. "macropod" wrote in message ... 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 |
#6
|
|||
|
|||
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 "Copwriter" wrote in message ... 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. "macropod" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Struggling with MS Query... | Alex | General Discussion | 5 | July 6th, 2004 11:46 AM |
macro to change chart on excel data field | [email protected] | Charts and Charting | 3 | June 11th, 2004 05:16 PM |
Too many data points in Excel Chart | Jo | Charts and Charting | 1 | April 5th, 2004 03:53 PM |