Microsoft Office Forums - View Single Post - 'Monday to Friday' Date calculation help needed

macropod · 12-13-2014, 07:49 PM

The get a Monday start date for the 'dates of availability between', change the 'jd':
{SET D8jd{=jd+84}}
in the third field in D8 to 'INT(jd/7)*7', thus:
{SET D8jd{=INT(jd/7)*7+84}}

As for what the field codes are doing, in B2, the:
{ASK StartDate "Issue Date"}
simply asks for the "Issue Date". The:
{SET a{=INT((14-{StartDate \@ M})/12)}}
{SET b{={StartDate \@ yyyy}+4800-a}}
{SET c{={StartDate \@ M}+12*a-3}}
{SET d{StartDate \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}}
calculates the Julian day number for the "Issue Date". The Julian day number is then used as the starting point for calculating all the other dates.

The:
{SET Tgtjd{=jd+126}}
{SET Tgte{=INT((4*(Tgtjd+32044)+3)/146097)}}
{SET Tgtf{=Tgtjd+32044-INT(146097*Tgte/4)}}
{SET Tgtg{=INT((4*Tgtf+3)/1461)}}
{SET Tgth{=Tgtf-INT(1461*Tgtg/4)}}
{SET Tgti{=INT((5*Tgth+2)/153)}}
{SET Tgtd{=Tgth-INT((153*Tgti+2)/5)+1}}
{SET Tgtm{=Tgti+3-12*INT(Tgti/10)}}
{SET Tgty{=100*Tgte+Tgtg-4800+INT(Tgti/10)}}
calculates the default values for the "Target Date" day, month and year, which the next field displays as the default when activated:
{ASK TargetDate "Target Date" \d "{Tgtd \# 00}/{Tgtm \# 00}/{Tgty}"}
In fact, if you remove the 'Tgt' prefix of these codes and the 'B2' etc. prefixes (which are simply the paragraph #s) from all the others, you'll see they're all essentially the same set of field codes. The reason for the prefixes is to ensure the bookmark values each SET field creates are unique to its own series of calculations. The # days to add to the original Julian day number is determined by the fields coded like:
{SET B2jd {=jd+14}}
For the fields where a Monday is needed, all that requires is to calculate the nearest preceding Julian day number that's exactly divisible by 7, which is obtained via INT(jd/7)*7. For Fridays, simply take that result and add 4.