Complex date comparison/Selection using fields instead of VBA

[Fields]

I have a bit of a nasty problem which I suspect only macropod or gmayor are going to be able to answer (first time poster, but have been lurking around reading their stuff for a few months), but I'll ask it for anyone that fancies having a go. I've been trawling for answers on this for a few months and made some progress but the final hurdle eludes me.

Due to IT restrictions, I can't use VBA for this, otherwise it'd be a doddle, so I need to do it in word fields.

I need to be able to take two dates (Date1, Date2), get a calculated date (CalcDate = Date1 +28 Days), and then display either Date2 or CalcDate, whichever is *earliest*.

I have succesfully implemented macropod's Date Calculation method to display CalcDate in another document. However, I'm having a bit of a head melt around expanding it out to a longer process (how to implement field codes isn't nearly as obvious as VBA...).

The way I think this should look is:
{QUOTE {
{Set Delay 28}
{Set Difference {= (Code from "Calculate the # Days Difference Between Two Dates" using Date1 & Date2)}
{IF {Difference} > {Delay} "{= (Code from "Calculate a day, date, month and year, using n days delay") referencing Date1}" "Date2"}
}}

Am I on the right track or am I missing something fairly obvious here?

[Fields]

Update:

I have separated this out into separate calculation fields to simplify things. I am getting an error

!Syntax Error, "

on the +n days calculation, which is set out as follows:

{SET Days{=
{SET a{... etc etc ...}
... etc ..
 
"{dd}-{mm}-{yy}" \@ "dd/MM/yyyy" }
}

When I copy the last line of the field calculations out and paste it, I get:"29-1-2012" \@ "dd/MM/yyyy"

Which is the correct date according to the Delay I've set.

Any Ideas why this isn't working? So close, and yet so far....

[macropod]

Try:

{QUOTE
{SET Delay 28}
{SET a{=INT((14-{DATE1 \@ M})/12)}}
{SET b{={DATE1 \@ yyyy}+4800-a}}
{SET c{={DATE1 \@ M}+12*a-3}}
{SET d{DATE1 \@ 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*(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)}}
{IF{DATE2 \@ "YYYYMMDD"}<{QUOTE "{dd}-{mm}-{yy}" \@ "YYYYMMDD"} {DATE2 \@ "dddd, d MMMM yyyy"}{QUOTE "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}}}

[Fields]

Thanks Paul

Bizzarely, that is giving me an answer of "6--22-2014" on a 28 day delay from a Date1 of 01/02/2012.

If I change the Day on Date1 (02/02/2012, 03/02/2012, etc) then I get "07--22-2014", "08--22-2014" etc. up to 25/02/2012 = "30--22-2014" and 26/02/2012 = "1--21-2014"

If I increase the Month (01/03/2012, 01/04/2012, etc) then I get "06--21-2014" "06--20-2014" up to 01/08/2012 = "6--16-2014" and 01/09/2012 = "6--27-2015"

For a Blank source date field it gives me "Wednesday, 28 March 2012".

This would be a lot easier if I could upload an attachment, but I can't do that at the moment.

My first guess was that I am running into a problem of System date setup. The local PC is set to dd/MM/yyyy format, but the file itself is held on a network server, which should also be set to the same format (and does display file dates in that format). The source field is set up as a Date-type entry field, restricted to dd/MM/yyyy format.

The standard "Find n days offset" code works perfectly in the same document, as does the "Find difference between two dates" code. Very strange.

[macropod]

What are DATE1 & DATE2? mergefields? formfield bookmarks? What regional date/time setting are you using? Do you have other fields in the document (eg for date calculation) that use the same bookmark names that the above field uses (see the SET fields)?

[Fields]

Originally Posted by macropod

What are DATE1 & DATE2? mergefields? formfield bookmarks? What regional date/time setting are you using? Do you have other fields in the document (eg for date calculation) that use the same bookmark names that the above field uses (see the SET fields)?

I have tried the following combinations for Date1 & Date2:
Both as fill-in formfield bookmarks (as standard text type and also as fixed dd/MM/yyyy Date types).
Date1 as formfield bookmark and Date2 as a { SET Date2 ... } parameter type.
All give the same results.

Regional settings should be either English/Uk or English/Ireland, but the workstation is locked down so I can't confirm that. All dates in the system, windows, and Word/Excel etc default to dd/MM/yyyy which would support the assumption, however.

There are 3 other calculations in the same document which re-use the "+n days" calculation, so they repeat the same bookmarks. These all work correctly, and reference the same Date1 Date2 bookmark information. I have copied the code above to a new blank document and recreated the fields, but the results are still the same.

If I quote out the result bookmarks, I get:
dd = "6"
mm = "-22"
yy = "2014"
so it does not look like the error is part of the formatting switch.


I tried retyping the code more than once, and tried copying the field codes directly from the Date Calculation doc to eliminate the possibility that I typed it incorrectly, but still got the same error. Having said that, I've just tried doing it in the blank document and I am getting the correct result!

Headwrecker!

[macropod]

If I add:
{ASK DATE1 "Date1?"}
{ASK DATE2 "Date2?"}
Just after the '{QUOTE' line, and input various dates, the field code works just fine. That suggests an error in your implementation. Perhaps you'll get better results if you copy the field code from my post into your document, then convert it to a field using the macro here: http://www.gmayor.com/export_field.htm#TextToField

[Fields]

Thanks Paul. I'd validated all the fields before, and re-done the code but couldn't get it working. Starting from scratch in a new document worked. Maybe it had genetic memory of an earlier error.

For reference, if I wanted to "set" this calculation to a variable, wa my original syntax workable, omitting the "quote" instead? E.g.

{set 28days{=
{Set delay 28... Etc}

this worked for "storing" the difference between two dates but not for the 28 day calculation. I think it might be due to my lack of understanding of the use of "quote" in the original code.

[macropod]

Hi Fields,

You could embed the entire QUOTE field in a SET field. If you convert the QUOTE field, everything after the SET field's bookmark name must be bounded by a pair of double quotes.

The most flexible way, though, is to work with the final IF test. Depending on what you want to do the the result, you might embed that whole field in a SET field, then output only the SET field's results both here and wherever else you want it, or you might use two SET fields with the same name inside the IF test, one for the true result, one for the false result. You could even do both - using different bookmarks names for the encompassing and embedded SET fields.