Results 1 to 15 of 34

20060827, 19:28 #1
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20060916, 05:30 #2
 Join Date
 Jul 2006
 Posts
 71
 Thanks
 1
 Thanked 0 Times in 0 Posts
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 20062007 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 20072008. 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}+4800a}}
... 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)

20060928, 09:35 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20061003, 05:58 #4
 Join Date
 Jul 2006
 Posts
 71
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20061030, 02:11 #5
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,174
 Thanks
 2
 Thanked 454 Times in 373 Posts
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(Month1,12)+1' throughout. Thanks for finding this. Looks like I'll have to update the tutorial!Cheers,
Paul Edstein
[MS MVP  Word]

20061106, 01:54 #6
 Join Date
 Jul 2006
 Posts
 71
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Date calculation (2003)
Hi, macropod,
Many thanks for the calculations!
axsc

20061201, 19:31 #7
 Join Date
 Dec 2006
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20061201, 20:11 #8
 Join Date
 Dec 2006
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20061201, 20:36 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20070123, 22:24 #10
 Join Date
 Jan 2007
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20070124, 09:07 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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}+Delay1,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.

20070125, 05:03 #12
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,174
 Thanks
 2
 Thanked 454 Times in 373 Posts
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}}Cheers,
Paul Edstein
[MS MVP  Word]

20070125, 21:15 #13
 Join Date
 Jan 2007
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20070125, 22:56 #14
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,174
 Thanks
 2
 Thanked 454 Times in 373 Posts
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#>Cheers,
Paul Edstein
[MS MVP  Word]

20070126, 15:17 #15
 Join Date
 Jan 2007
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>