[Slinky]

Since the "Date calculation discussions" thread starting at post 432,773 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.

[axsc]

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)

Attached file(s)

•  MeetingSchedule.doc (94.5K)
  Number of downloads: 11

[Slinky]

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.

[axsc]

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

[macropod]

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!

Attached file(s)

•  MeetingSchedule.doc (94K)
  Number of downloads: 23

[axsc]

Hi, macropod,

Many thanks for the calculations!

axsc

[Drexlin]

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.

[Drexlin]

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

[Slinky]

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.

[tktm]

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

[Slinky]

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.

[macropod]

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}}

[tktm]

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?

[Slinky]

I'm sure macropod will look at it soon, but it's early Friday morning in Australia at the moment...

[macropod]

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

[tktm]

Awesome! Thanks so much

[wdewey]

I was using the "Calculate a day, date, month and year, using n days delay" date calculation in a mail merge document and it appeared to work correctly at first. I am actually using two different versions of this in the same document, but the original in the V2.55 tutorial has the same problem. I the delay is 7 and it should show March 1 2007, but it actually shows January 03 2007. The funny thing is that the 30 day delay displays correctly. It shows March 24 2007. Everything appears to work correctly up to March 1, but after that additional delays come up with various dates that are often not close to the correct date. Then things start working again at 19 days delay. I am going to look through the tutorial, but at the moment I don't understand what the calculations do. Wanted to make you aware of the problem and if you get an opportunity to correct it I would greatly appreciate it.

I wanted to add that it looks like a lot of work went into this document and I really think it is great resource.

Bill

[wdewey]

Looking into this a little more, it appears that when the calculation is not working right that the Month and day are being swapped. The day stays as 03 while the month increments from January through December. After the 12th of March things start working right again. Thought this might help figure out what the problem is.

Bill

[wdewey]

It looks like (on setup here) that changing the input into the display from "{dd}-{mm}-{yy}" to "{mm}-{dd}-{yy}" fixed this problem. I wonder if it has to do with a default date/time setting. O'well, calculations work great! Thanks so much for this wonderful resource!

Bill

[Slinky]

Welcome to Woody's Lounge!

The section named "Date input/output formats" under "Introductory Notes" near the beginning of Macropod's document mentions this issue explicitly:

---

Many date fields in this document are region-dependent and are coded to work on systems using day-month-year regional settings. For system using month-day-year regional settings, obtaining a correctly result is as simple as changing the order of the embedded fields coded as