Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-02-2020, 07:55 AM
mode1111 mode1111 is offline Mail merge rounding error for times? Windows 10 Mail merge rounding error for times? Office 2016
Novice
Mail merge rounding error for times?
 
Join Date: Mar 2020
Posts: 3
mode1111 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 03-02-2020, 02:52 PM
macropod's Avatar
macropod macropod is offline Mail merge rounding error for times? Windows 7 64bit Mail merge rounding error for times? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,434
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Why do you want the Excel workbook open (and editable) whilst running a mailmerge?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 03-03-2020, 07:35 AM
mode1111 mode1111 is offline Mail merge rounding error for times? Windows 10 Mail merge rounding error for times? Office 2016
Novice
Mail merge rounding error for times?
 
Join Date: Mar 2020
Posts: 3
mode1111 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 03-03-2020, 02:28 PM
macropod's Avatar
macropod macropod is offline Mail merge rounding error for times? Windows 7 64bit Mail merge rounding error for times? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,434
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
  #5  
Old 03-04-2020, 09:26 AM
mode1111 mode1111 is offline Mail merge rounding error for times? Windows 10 Mail merge rounding error for times? Office 2016
Novice
Mail merge rounding error for times?
 
Join Date: Mar 2020
Posts: 3
mode1111 is on a distinguished road
Default

Thanks so much for the reply.
Yeah! that worked well and also fixed the AM/PM problem I had.

Thanks so so much!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding to split up times. GregM Excel 1 03-27-2019 11:58 PM
Mail merge rounding error for times? Rounding a merge calculation to whole number finners01 Mail Merge 1 12-03-2018 04:55 AM
Mail merge rounding error for times? Mail Merge Repeat Record a certain number of times stacey_e Mail Merge 6 11-19-2018 09:38 PM
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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft