Excel Formats and IF Sara 10/30/2008 3:39:01 AM Hi there I'm doing a mailmerge from an excel spreadsheet. It has a variety of time values, dollar amounts, and rounding. While I have worked out how to write the code to include the formatting, how do I extend this so that blanks show as either nothing or N/A? Time fields currently have this: { MERGEFIELD Mon_Start \[ at ]HH:mm } Blank time fields are showing as "00:00" and I need them to be blank Rounded fields currently have this: { MERGEFIELD Hourly_Rate \# "0.000000" } Blank fields are showing as "0.000000" and I need them to be "N/A"

Re: Excel Formats and IF "Doug Robbins - Word MVP" <dkr[ at ]REMOVECAPSmvps.org> 10/30/2008 4:40:25 AM { IF { MERGEFIELD Mon_Start \[ at ]HH:mm } = "00:00" " " { MERGEFIELD Mon_Start \[ at ]HH:mm } } { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } or { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \[ at ]HH:mm } } { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Sara" <Sara[ at ]discussions.microsoft.com> wrote in message news:332BCFF5-46E8-4873-BFFC-A0819A7B5CE1[ at ]microsoft.com... [Quoted Text] > Hi there > > I'm doing a mailmerge from an excel spreadsheet. It has a variety of time > values, dollar amounts, and rounding. > While I have worked out how to write the code to include the formatting, > how > do I extend this so that blanks show as either nothing or N/A? > Time fields currently have this: > { MERGEFIELD Mon_Start \[ at ]HH:mm } > Blank time fields are showing as "00:00" and I need them to be blank > Rounded fields currently have this: > { MERGEFIELD Hourly_Rate \# "0.000000" } > Blank fields are showing as "0.000000" and I need them to be "N/A" > >

Re: Excel Formats and IF "Graham Mayor" <gmayor[ at ]REMOVETHISmvps.org> 10/30/2008 6:29:21 AM A simpler alternative for the hourly rate format would be { Mergefield Hourly_Rate \# "0.00000;;N/A" } -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Doug Robbins - Word MVP wrote: [Quoted Text] > { IF { MERGEFIELD Mon_Start \[ at ]HH:mm } = "00:00" " " { MERGEFIELD > Mon_Start \[ at ]HH:mm } } > > { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { > MERGEFIELD Hourly_Rate \# "0.000000" } } > > or > > { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \[ at ]HH:mm > } } > { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD > Hourly_Rate \# "0.000000" } } > > > "Sara" <Sara[ at ]discussions.microsoft.com> wrote in message > news:332BCFF5-46E8-4873-BFFC-A0819A7B5CE1[ at ]microsoft.com... >> Hi there >> >> I'm doing a mailmerge from an excel spreadsheet. It has a variety >> of time values, dollar amounts, and rounding. >> While I have worked out how to write the code to include the >> formatting, how >> do I extend this so that blanks show as either nothing or N/A? >> Time fields currently have this: >> { MERGEFIELD Mon_Start \[ at ]HH:mm } >> Blank time fields are showing as "00:00" and I need them to be blank >> Rounded fields currently have this: >> { MERGEFIELD Hourly_Rate \# "0.000000" } >> Blank fields are showing as "0.000000" and I need them to be "N/A"

Re: Excel Formats and IF Sara 10/30/2008 9:04:15 PM What am I doing wrong? If I put this in: { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \[ at ]HH:mm } } The merge result is "Mon_Start" rather than the actual value from the spreadsheet If I put this in: { IF { MERGEFIELD Mon_Start \[ at ]HH:mm } = "00:00" " " { MERGEFIELD Mon_Start \[ at ]HH:mm } } The merge result is "Error! Too many picture switches defined" "Graham Mayor" wrote: [Quoted Text] > A simpler alternative for the hourly rate format would be > > { Mergefield Hourly_Rate \# "0.00000;;N/A" } > > -- > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Graham Mayor - Word MVP > > My web site www.gmayor.com > Word MVP web site http://word.mvps.org > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > > > Doug Robbins - Word MVP wrote: > > { IF { MERGEFIELD Mon_Start \[ at ]HH:mm } = "00:00" " " { MERGEFIELD > > Mon_Start \[ at ]HH:mm } } > > > > { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { > > MERGEFIELD Hourly_Rate \# "0.000000" } } > > > > or > > > > { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \[ at ]HH:mm > > } } > > { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD > > Hourly_Rate \# "0.000000" } } > > > > > > "Sara" <Sara[ at ]discussions.microsoft.com> wrote in message > > news:332BCFF5-46E8-4873-BFFC-A0819A7B5CE1[ at ]microsoft.com... > >> Hi there > >> > >> I'm doing a mailmerge from an excel spreadsheet. It has a variety > >> of time values, dollar amounts, and rounding. > >> While I have worked out how to write the code to include the > >> formatting, how > >> do I extend this so that blanks show as either nothing or N/A? > >> Time fields currently have this: > >> { MERGEFIELD Mon_Start \[ at ]HH:mm } > >> Blank time fields are showing as "00:00" and I need them to be blank > >> Rounded fields currently have this: > >> { MERGEFIELD Hourly_Rate \# "0.000000" } > >> Blank fields are showing as "0.000000" and I need them to be "N/A" > > >

Re: Excel Formats and IF "Graham Mayor" <gmayor[ at ]REMOVETHISmvps.org> 10/31/2008 6:25:44 AM Are you using CTRL+F9 for the field boundary pairs {} ? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Sara wrote: [Quoted Text] > What am I doing wrong? > > If I put this in: > { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \[ at ]HH:mm > } } The merge result is "Mon_Start" rather than the actual value from > the spreadsheet > If I put this in: > { IF { MERGEFIELD Mon_Start \[ at ]HH:mm } = "00:00" " " { MERGEFIELD > Mon_Start \[ at ]HH:mm } } > The merge result is "Error! Too many picture switches defined" > > > > > "Graham Mayor" wrote: > >> A simpler alternative for the hourly rate format would be >> >> { Mergefield Hourly_Rate \# "0.00000;;N/A" } >> >> -- >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> Graham Mayor - Word MVP >> >> My web site www.gmayor.com >> Word MVP web site http://word.mvps.org >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> >> >> Doug Robbins - Word MVP wrote: >>> { IF { MERGEFIELD Mon_Start \[ at ]HH:mm } = "00:00" " " { MERGEFIELD >>> Mon_Start \[ at ]HH:mm } } >>> >>> { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { >>> MERGEFIELD Hourly_Rate \# "0.000000" } } >>> >>> or >>> >>> { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start >>> \[ at ]HH:mm } } >>> { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD >>> Hourly_Rate \# "0.000000" } } >>> >>> >>> "Sara" <Sara[ at ]discussions.microsoft.com> wrote in message >>> news:332BCFF5-46E8-4873-BFFC-A0819A7B5CE1[ at ]microsoft.com... >>>> Hi there >>>> >>>> I'm doing a mailmerge from an excel spreadsheet. It has a variety >>>> of time values, dollar amounts, and rounding. >>>> While I have worked out how to write the code to include the >>>> formatting, how >>>> do I extend this so that blanks show as either nothing or N/A? >>>> Time fields currently have this: >>>> { MERGEFIELD Mon_Start \[ at ]HH:mm } >>>> Blank time fields are showing as "00:00" and I need them to be >>>> blank Rounded fields currently have this: >>>> { MERGEFIELD Hourly_Rate \# "0.000000" } >>>> Blank fields are showing as "0.000000" and I need them to be "N/A"