# Thread: Date calculation discussions ctd. (2.46 and later)

1. ## Date calculation discussions ctd. (2.46 and later)

Since the "Date calculation discussions" thread starting at <post:=432,773>post 432,773</post:> has become very long, it has been locked. Please feel free to post your questions about Macropod's Date Calculations in Word (2.46) in this thread.

2. ## Re: Date calculation (2003)

I have been enjoying trying to encourage macropod's inspired code to calculate my meeting dates, and for the most part I now seem to have it doing so. What I wanted was to be able to create a file with a set of 2006-2007 meeting dates -- the first Saturday of the month -- so that NEXT summer, when I need to update and reprint the file, the dates will automatically update for 2007-2008. And so on, and so on, into the distant future.

I did end up rearranging some lines, so the first few now read in this form:
{SET Month #}
{SET Week #}
{SET Weekday #}
{SET Year {={DATE @ yyyy}+#}}
{SET b{=(Year}+4800-a}}
... rest of calculation, format, etc.

I like this because it's then easy to check the date information right at the start, and I can specify the year I need the date for.

Most months are duly producing the first Saturday just as desired. HOWEVER, for some reason I can't work out, the December formula provides first Saturday in December for the previous year. I don't see any obvious reason why it should. File attached with a table of This Year, Next Year, Two Years from now, to illustrate the difficulty.

Weird query: I managed to tweak the code to identify Kentucky Derby Saturday (first Saturday in May) and Preakness Saturday (third Saturday in May). However, the Belmont is run 21 days after the Preakness, a Saturday which falls between June 2 and June 8 -- as you see, it is usually, but not always, the first Saturday in June. My efforts to add 21 days to the third Saturday in May caused a problem -- no doubt with the Limit code line -- and the date currently reads 40 May 2006. This is probably correct, so to speak, but a date given as "June" something would be preferable! Would it be preferable approach to attempt to add 21 days to the third Saturday in May, or would it be easier to specify "Saturday" with limits set to >June 1 and <June 9??

Many thanks,

axsc
(no hurry -- leaving town for a week in the morning)

3. ## Re: Date calculation (2003)

Macropod is on vacation at the moment, so you'll have to wait some weeks before he'll be able to reply.

His brilliant field codes are excellent for occasional date calculations, but Word is not the most appropriate application for large numbers of calculations, It would be much easier to accomplish what you want in Excel, since it is geared towards working with calculations and formulas, and since it has a large number of date functions built in.

4. ## Re: Date calculation (2003)

It's an excellent suggestion, but I'm using these calculations in documents for people who can cope -- more or less -- with Word, but seldom with links between Excel and Word. So the Word calculations are most helpful. In any case, I have now solved the horse race problem -- added a Delay of 3 weeks to the Preakness date, which seems to work well and was less complicated than 21 days or a range of dates. Have also worked out a calculation for Mardi Gras which also seems to work well.

Am still wondering why a December calculation will produce a date a year off, though.

Hope Macropod has had a splendid vacation and returns from it -- eventually -- very fit and raring to go.

axsc

5. ## Re: Date calculation (2003)

Hi asxc,

Attached is a revised copy of your meeting schedules document.

To calculate the Belmont event date as 21 days offset from the Preakness date, I first changed the 'dd', 'mm' and 'yy' bookmark names in the Belmont field to 'pdd', 'pmm' and 'pyy', respectively. Then I modified the formula found under 'Calculate a day, date, month and year, using n (301) days delay' in my tutorial document to refer to those bookmarks instead of the system date and changed the 'Delay' value from 301 to 21.

As for the December anomaly, I fixed it by changing the expression 'MOD(Month,12)' to 'MOD(Month-1,12)+1' throughout. Thanks for finding this. Looks like I'll have to update the tutorial!

6. ## Re: Date calculation (2003)

Hi, macropod,

Many thanks for the calculations!

axsc

7. ## Re: Date Calculations (2003)

I was wondering if you can help me write code to subract one from a date. The data is imported from an excel spreadsheet. My attempt at changing your code was futile. Any help would be appreciated.

Thanks.

8. ## Re: Date Calculations (2003)

The problem I am having now, is that I have white space where the code is supposed to be. Why is this?

9. ## Re: Date Calculations (2003)

Welcome to Woody's Lounge!

Could you post a copy of the document with the date and with your attempt at the calculated field? You can remove everything else from the copy.

10. ## Re: Date calculation discussions ctd. (2.46 and later)

Hi, I just read macropod's document and it is Fantastic (THANKS!) but I had a slight problem.

At the "Automatically Insert a Past or Future Date" section, any that calculate a date using n months delay seem to trip at the change of a year. For example, it is now January. If I want to set the delay to -1 (go back one month) it still returns January. Some of the others trip up completely. I didn't see this problem addressed yet in the previous threads, if I have missed it, can someone please point me to the discussion?

Thanks

11. ## Re: Date calculation discussions ctd. (2.46 and later)

Welcome to Woody's Lounge!

Apparently, the formulas don't handle negative delays correctly.
Select the entire field and press Shift+F9 to display field codes.
Part of the field code will look like

{=MOD({DATE @ M}+Delay-1,12)+1}

Change the -1 after the word Delay to +1199

{=MOD({DATE @ M}+Delay+1199,12)+1}

This effectively adds 1200 months to the delay; this is sufficient to handle negative delays up to -1200 months.
Press F9 to hide the field codes and update the field.

12. ## Re: Date calculation discussions ctd. (2.46 and later)

Hi tktm,
I'll be posting an update ASAP. In the meantime, you can use:
{SET mm{=MOD(ABS({DATE @ M}+Delay+11),12)+1}}

13. ## Re: Date calculation discussions ctd. (2.46 and later)

Thanks macropod and HansV! Those both worked like a charm on rolling the month back, but the corresponding year still isn't coming out right.

update: I've narrowed the problem down to the fact that while Excel says the INT(-.08333) is -1, Word persists in telling me it is 0. Anybody know a workaround for Word's abuse of mathematical conventions?

14. ## Re: Date calculation discussions ctd. (2.46 and later)

Hi tktm,

That too has been fixed, via:
{SET yy{=INT({DATE @ yyyy}+(Delay+{DATE @ M}-1)/12)}}

You can now download the update Date Calc 'tutorial' in <post#=249902>post 249902</post#>

15. ## Re: Date calculation discussions ctd. (2.46 and later)

Awesome! Thanks so much <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•