Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-31-2011, 11:46 PM
Foxfire Foxfire is offline Using the Delay fields Windows 7 32bit Using the Delay fields Office 2010 32bit
Novice
Using the Delay fields
 
Join Date: Jul 2011
Posts: 3
Foxfire is on a distinguished road
Default Using the Delay fields

Hi Guys,

What I'm trying to accomplish is to create an invoice. I work with real estate software and the field I'm trying to calculate the invoice based off, is the tenants paid to date, which is already set up as a merge field. I'm also Australian so my dates are dd/mm/yyyy.

So for example I need a tenant with a paid to date of 31/07/2011 to display the following two dates.

Invoice #blah
You owe rent for one month being 01/08/2011 to 31/08/2011.

I've done a bit of internet searching (which I'm pretty bad at) and the only thing I could find that really helped was the big setup below.

I'm looking for two things. One if someone could explain what is happening below so I can understand what I'm typing in and also if there is an easier way to do that's been found/developed in the past couple of years as the field below is from 2009.

The second thing I need is how to calculate the second date on the invoice accurately. A delay date of 31 or 30 doesn't work accurately for every monthly period. The from date I have sorted as I used the below field with the delay of 1. The only way I can think of doing this one is with a massive 'if ... then' imbeded tree that checks what month the date is in and uses the appropiate delay then.

I also couldn't work out how copypaste the merge field itself, without it pulling through data . Although by typing it in I did start to understand it a lot more.

The initial lines are all about calculating the days, months and years based on the merge field, then adding them and subtracting them to get the right dd/mm/yy to display. I still don't understand the INT or the figures used however.

I got the following date calculation from the datecalcs.zip on http://www.gmayor.com/downloads.htm it was originally done by Paul Edstein (Macropod) and mad props to him for doing some really awesome stuff.

{quote "Invoice From "
{set Delay 1}
{set a{=int((14-{MERGEFIELD "tenant_Paid_To" \@ M}/12} }


{set b{={MERGEFIELD "tenant_Paid_To" \@ yyyy}+4800-a} }
{set c{={MERGEFIELD "tenant_Paid_To" \@ M}+12*a-3} }
{set d{MERGEFIELD "tenant_Paid_To" \@ d} }
{set jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay} }
{set e{=INT((4*(kd+32044)+3)/146907)} }
{set f{=jd+32044-INT(146097*e/4)} }
{set g{=INT((4*f+3)/1461} }
{set h{=f-INT(1461*g/4)} }
{set i{=INT((5*h+2)/153)} }
{set dd{=h-INT((153*i+2)/5)+1} }
{set mm{=i+3-12*INT(i/10)} }
{set yy{=100*e+g-4800+INT(i/10)} }
"{dd}-{mm}-{yy}"
}
Reply With Quote
  #2  
Old 08-11-2011, 05:14 AM
macropod's Avatar
macropod macropod is offline Using the Delay fields Windows 7 64bit Using the Delay fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Foxfire,

The best way to do this sort of thing is to modify the data source, so that it calculates the correct dates from the outset. That way, there's nothing left for the mailmerge to work out.

If that's not practical, you can adapt the tutorial example titled 'Calculate a lease expiry date, using n months delay', by adding a new line, thus:
{SET StartDate {MERGEFIELD "Tenant_Paid_To"}}
after the '{SET Delay 12}' line (modified to'{SET Delay 1}') and changing all the 'DATE' references to 'StartDate'.

You should end up with a field code that looks like:
{QUOTE
{SET Delay 1}
{SET StartDate {MERGEFIELD "Tenant_Paid_To"}}
{SET m{=MOD(ABS({StartDate \@ M}+Delay+11),12)+1}}
{SET y{=INT({StartDate \@ yyyy}+(Delay+{StartDate \@ M}-1)/12)}}
{SET a{=INT((14-m)/12)}}
{SET b{=y+4800-a}}
{SET c{=m+12*a-3}}
{SET d{=IF(({StartDate \@ d}>28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)))*(m=2)=1,29+((MOD(yy,4)=0)+(MOD(y y,400)=0)-(MOD(yy,100)=0)),{StartDate \@ d})}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045-1}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

Or you can copy/paste the above into your document and use the macro I developed at: http://www.gmayor.com/export_field.htm#TextToField to convert it to a working field code.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 08-14-2011, 11:35 PM
Foxfire Foxfire is offline Using the Delay fields Windows 7 32bit Using the Delay fields Office 2010 32bit
Novice
Using the Delay fields
 
Join Date: Jul 2011
Posts: 3
Foxfire is on a distinguished road
Default

Yeah after I posted I thought about it for a bit and went back through the Datecalcs document and found the month delay field, which works a treat.

I still don't understand all your calculations but it seems to work fine, except for February.

Apparently with February, on the day delay field it needs a delay set of 3 instead of 1 and I just don't understand enough of the code you've put in there to adjust it.
Reply With Quote
  #4  
Old 08-15-2011, 02:35 AM
macropod's Avatar
macropod macropod is offline Using the Delay fields Windows 7 64bit Using the Delay fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Foxfire View Post
Apparently with February, on the day delay field it needs a delay set of 3 instead of 1 and I just don't understand enough of the code you've put in there to adjust it.
I don't understand: If the month ends in February, why would the month delay value be any different? As coded, the field allows for February to have either 28 or 29 days, depending on whether it's a leap year.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 08-15-2011, 03:49 PM
Foxfire Foxfire is offline Using the Delay fields Windows 7 32bit Using the Delay fields Office 2010 32bit
Novice
Using the Delay fields
 
Join Date: Jul 2011
Posts: 3
Foxfire is on a distinguished road
Default

Thanks for the replies by the way, I really appreciate the help.

I have an SQL database I'm pulling the date fields from and here is what happens. Unfortunately I can't modify the date in the database at all, so I have to do it all on the letter.

Paid_to_date = 16/08/2011.

Invoice from <delay by 1 day> to <delay by 1 month>

The above merge fields pull through the following information.

Invoice from 17/08/2011 to 16/09/2011.

However without changing anything, if I create the letter from a file with a paid to date anywhere in February, it does the following:

Paid_to_date = 14/02/2011

Invoice from 12/02/2011 to 14/03/2011.

If I set the delay to 3, instead of 1. February then works fine. Maybe I typed something wrong. When I've a chance I'll double check it. But if the above field is correct, my letter should be correct.
Reply With Quote
  #6  
Old 08-20-2011, 02:40 AM
macropod's Avatar
macropod macropod is offline Using the Delay fields Windows 7 64bit Using the Delay fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Foxfire,

I am unable to replicate that behaviour. Are you sure your SQL DB outputs the dates in the correct (ie dd/mm/yy) format?

What happens if you make a copy of the field and change:
{SET StartDate {MERGEFIELD "Tenant_Paid_To"}}
to:
{ASK StartDate "Tenant_Paid_To?"}
and update the field manually (via F9) and inputting various dates?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
delay sending email and follow up on all emails lefteris Outlook 1 05-17-2011 05:11 PM
Using the Delay fields Delay delivery Registrar Outlook 1 07-01-2010 10:17 AM
Using the Delay fields Delay/Pause animated photo prior to next clipart BettyC PowerPoint 2 04-19-2010 02:12 PM
Email send delay--where? markg2 Outlook 8 02-14-2010 03:40 PM
How to delay sending an invitation... imported_misterbobthetomato Outlook 0 07-20-2006 07:42 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:05 AM.


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