Hi,

Something I have been trying to get my head around for ages and just not succeeding with is automatic date calculation in word.



At my office we have a document in which a number of key future dates need to be entered, apart from those dates no text changes, so in order to speed things up for people I've tried & failed to add some kind of date function.

I've read an excellent guide on Word date & time manipulation which went pretty much straight over my head, and sadly if I don't get it my colleagues certainly won't

I've got 8 dates which need to be calculated, 6 of which are relatively straight forward.

However, the two I'm really struggling with involve two periods of between dates, for example Monday xx xx xxxx to Friday xx xx xxxx. So the date the document was issued is not an exact set period for calculation, ie the Mon-Fri dates need to be the same regardless of the day of the week the document was issued.

It's this bit my brain cannot contend with and that's why I'm asking for help.

I've attached a basic page which is the 'gist' of what I'm after. The text highlighted in blue is where date calculation needs to take place.

Any help given will be greatly received.


Ballst

Attached Files

DateCalculation.doc (39.5 KB, 12 views)

Presumably you're referring to my Microsoft Word Date Calculation Tutorial, at:
http://windowssecrets.com/forums/sho...ation-Tutorial
or:
http://www.gmayor.com/downloads.htm#Third_party

Try the attached. To update the dates, simply press Ctrl-A, F9, then input a new 'issue date'.

Attached Files

DateCalculation.doc (59.0 KB, 14 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hello again Macropod!

Yes, having checked the guide you are indeed the author, I originally downloaded it for a very simple date query in 2008, but this more advanced problem has been on my backburner since 2012....

I've tried the attachment you supplied but with mixed/limited results. On first run using todays date (11/12/2014) it worked, but with any subsequent changes to the issue date box the fields do not update, and on one occassion all reverted to the same date.

I have tried to study the field codes in the document, and whilst they make a little more sense, not enough for me to know what to do and this does need to be 'user proof'

The final thing was that in Point D in the document there are actually three date calculations needed. The first and second are the between Monday to Friday calculations, which if 11/12/2014 was the issue date the answer would be Monday 26/01/2014 to Friday 3001/2014. The third calculation is the Monday 12 weeks after the date of issue, so if issued 11/12/2014 the answer should be Monday 02/03/2015

My apologies that I did not make that clearer in my original post.


Ballst

As I said, you must select the entire document (via Ctrl-A) before updating the issue date. Do that and all the calculations will update. As for your point D, adding the extra calculations is trivial, but it's far from clear as to what the text is actually supposed to say. And, as for the actual date, since 11/12/2014 is a Thursday, should the 7 & 12 weeks count from the preceding Monday, or from the Next Monday?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I can confirm that I am using Select All / Ctrl-A before updating the issue date and it doesn't seem to make a difference, as per the attached.

Re Point D, the 7 & 12 weeks should count from the preceeding Monday.

Regarding the actual text, it says something along the lines of... Between Monday dd/mmm/yyyy - Friday dd/mmm/yyyy parties are to return document outlining dates of availablity between dd/mmm/yyyy and dd/mmm/yyyy which is the target date (this date is manually entered, as it is not fixed.)

Hope this helps.

Attached Files

DateCalculationA.doc (52.0 KB, 7 views)

Try the attached revision. It now also offers a default 'target date' that is 13 weeks (3 months) after the start of the 12-week period. A different default period could be chosen, but the proffered default date can be overtyped anyway.

Attached Files

DateCalculation.doc (67.0 KB, 11 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Macropod,

Thanks again for the reply.

I've been trying this one again this morning, but with only limited success.

1. The dates now automatically calculate, so thank you. However as soon as you print, or even preview print all the dates revert to the same day. For example if you take 01/01/2015 as date of issue all the other dates revert to 26/03/2015. The dates will remain as incorrect until you change the Issue date, however again any attempt to print renders it faulty.

2. Re the dates in Point D, for info the target date should be 126 days from the date of issue, so the flexibility of the additional input box is a great solution for those that slip. For reference the first date of availability is always a Monday, so it could perhaps be linked to the first date calculation in D, i.e the date the document is to be returned? If so the first week of availability is always 5 weeks after the document is due.


Ballst

Try it now.

Attached Files

DateCalculation.doc (72.5 KB, 9 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi,

Viewing this at home and there doesn't seem to be a problem with the dates changing when printing.

The only thing now remaining is....


The first date here has got to be a Monday, the dates in this section are always week commencing.

I've tried to edit your code for this one, but sadly my lack of ability has held me back...

If you have the time I'd really appreciate a quick explanation of what the code in the backgound is doing.

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.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Macropod,

Only just had a chance to have a look at this today, but I had a go, and seem to have it working, so thank you very much.

The explanation makes sense as well, so I might be brave and try and stick this into another document I'm playing around with.

Anyway, Seasons greetings and thanks again for your help!