Jump to content
You must now use your email address to sign in [click for more info] ×

Publisher Data Merge number precision

Recommended Posts


Hopefully I'm posting this question in the right place.

I'm using data merge to pull through time results from excel, all fine with that.  In excel I have the cell formatted to m:ss.sss (ie 1:34.572) as its to do with accurate time splits.  However when this comes through Publisher is rounding this, so rather than being 1:34.572 it comes through as 1:35.  Is there a way to change the precision of the text coming through so its formatted to the same?

I assume the text box can be formatted to match this however I can't find the option for this, can anyone advise?


Link to comment
Share on other sites

My ancient excel doesn't understand m:ss.sss and it gets shortened to m:ss.ss so the seconds are repeated
m:ss.000 works but Publisher V1 doesn't understand it so the only way I image.png.02ced21c42af8f8dedc9e874f7e2fc4a.pngcould get ms to work in a data merge is to use a csv file rather than xlsx

Microsoft Windows 10 Home, Intel i7-9750H CPU @ 2.60GHz, 16 GB RAM, 500GB SSD, 1TB Whirlygig, NVIDIA GeForce RTX 2060
Affinity Photo - 24/05/20, Affinity Publisher - 06/12/20, KTM Superduke - 27/09/10

Link to comment
Share on other sites

Hi David,

Thanks for your response, never considered that csv is a better way to do data merge, that's resolved this.

You are also correct with your note on formatting in excel, it is mm.ss.000, not mm.ss.sss as I suggested as that just repeats the seconds number.


Link to comment
Share on other sites

You can use Excel sheet as data source but need to use TEXT() function to format data as text (note below that formatting notation is regional, so below the arguments are separated using a semicolon, the decimal is given with a comma and hour with a "t" (for "tunti", Finnish for hour)). English notation would be TEXT(A2, "h:mm:ss.000").

TIME() function would return rounded seconds, but e.g. NOW() returns an unrounded value. If exact values are wanted to be returned with TIME(), you would enter something like =TIME(0;12;32)+0,24/24/60/60., or when using English regional settings, =TIME(0,12,32)+0.24/24/24/60) 


Link to comment
Share on other sites

Hi Lacaerto,

Thanks for this option too, and the explanation how it works.  I tried to covert to text before posting but got my formula wrong for it.  It works too and gives me an option to keep using excel.


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.