#1
|
|||
|
|||
Mail merge rounding error for times?
Hi all, So I'm using mail merge to transfer time data from excel to word. The format I input to excel is HH:mm AM/PM. It originally did have a computing problem when both excel and word were open at the same time, giving a decimal number instead of time, so now I use this: {QUOTE{SET tval{MERGEFIELD Time_sampled}} {IF tval="?:*" {tval \@ "HH:mm"}" {IF tval="??:*"{tval \@ "HH:mm"}"{=INT(tval*24) \#00}:{=INT(60*((tval*24)-INT(tval*24))) \#00}"}"}} which basically should (hopefully) turn any "decimal time" into normal time in HH:mm (without the AM/PM...that didn't work for some reason). It works well except for one thing: all minutes are shifted by 1 minute down, so a 3:25 PM in excel will transfer to word as 15:24. I've also noticed that this problem occurs only when you open the excel first and then the word but disappears if you open the word first and then excel...problem is that then excel is "read-only". So if anyone can help me with this bizarre issue... I have no idea what to change. Thanks so much in advance, Alex. |
#2
|
||||
|
||||
Why do you want the Excel workbook open (and editable) whilst running a mailmerge?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Oh, I just work on both simultaneously. So I have some data in excel and I write reports. It is just more convenient to have the data open if anything needs to be changed. I prefer not to open and close documents for each and every report.
|
#4
|
||||
|
||||
Try:
{QUOTE{SET tval{MERGEFIELD Time_sampled}}{IF{=tval}={REF tval} "{SET ss{=tval*86400 \# 0}}{SET hr {=INT(ss/3600) \# 0}}{SET mn {=INT((ss-hr*3600)/60) \# 0}}{SET AMPM{IF{hr}< 12 "AM" "PM"}}{=MOD(hr+11,12)+1 \# 0}:{mn \# 00} {REF AMPM}" {tval \@ "hh:mm AM/PM"}}}
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks so much for the reply.
Yeah! that worked well and also fixed the AM/PM problem I had. Thanks so so much! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge Repeat Record a certain number of times | stacey_e | Mail Merge | 7 | 07-07-2023 01:27 AM |
Rounding to split up times. | GregM | Excel | 1 | 03-27-2019 11:58 PM |
Rounding a merge calculation to whole number | finners01 | Mail Merge | 1 | 12-03-2018 04:55 AM |
Using 3 different fields, several times in same document but not en mass - should I use mail merge? | shoughton | Mail Merge | 1 | 06-24-2015 02:46 PM |
Mail merge error? | SRE | Outlook | 0 | 01-11-2010 05:54 PM |