Thread: [Solved] Using the Delay fields
View Single Post
 
Old 07-31-2011, 11:46 PM
Foxfire Foxfire is offline Windows 7 32bit Office 2010 32bit
Novice
 
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