[macropod]

Attached is a zipped Word file I've put together showing how to do date & time calculations using fields in Word.

Topics covered include:

. Date & Time Field Basics
. Converting between Gregorian Calendar Dates, Julian Calendar Dates and Julian Day Numbers
. Converting Between Dates and Days of The Year
. Automatically Insert A Past Or Future Date, Adjusted By +/- A Number Of Days, Weeks etc,
. Dealing with Weekends and Holidays in Calculated Dates
. Interactively Calculate A Past Or Future Date
. Date and Time Calculations In a Mailmerge or a Word Form
. Date and Time Calculations In a Table
. Express Today's Date In Fiscal Year Terms
. Calculate the # Days Difference Between Two Dates
. Calculate the # Years Months & Days Difference Between Two Dates
. Calculate Whether a Period Has Elapsed
. Interactively Calculate a Person's Age
. Calculate a Stepped Date
. Calculate a Stepped Date Range
. Calculate a Date Sequence
. Calculate Dates of Easter
. Use Date (and/or Time) Comparisons to Vary Text
. Converting Numeric Date Strings into Word Date Formats
. Importing Date and Time Values from Excel and Access
. Add or Subtract Two Time Periods
. Calculate a Past or Future Time of Day
. Calculate a Future Date & Time

Enjoy.

Feedback welcome.

This thread became very long and has been locked.

Discussions continued in Post 432773 which also became very long and has been locked.

Please feel free to post your questions thread starting at Post 595560

Cheers

Attached file(s)

•  DateCalc.zip (85K)
  Number of downloads: 808

This post has been edited by macropod: 2009-12-18 01:29
Reason for edit: Minor bug fix, new field added for calculation of date & time

[Phil Rabichow]

This is fantastic! Can't say that I will have the time to test it's many nuances, but I've looked at your overall structure using SET & QUOTE fields.

[Slinky]

Excellent piece of work, macropod!
I'm sure it will forestall many future questions, so it should help in keeping your post count low.

[legalbear2001]

Huloo - this collection of goodies looks to be handy!

However, I've run into a snag using the "Interactively Calculate a Past or Future Date" routines (the same problem happens in all 3 version). If you use 31/07/2003 as the starting date and -7 as the variable, it correctly returns 24 Jul 2003. However, if you try 01/08/2003 with the same variable, it reads the 01/08 as January 8th instead of August 1st!!

I thought maybe it was something with values less than 12 (number of months), but if you use 07/07/2003 and -7 it will correctly return 30 Jun 2003. What's gone wrong here???

I'm *really* hoping you can fix this, as I've got a project going where this would serve QUITE handily, though I'd have to change how it displays things though. What I'm shooting for is something that'll prompt a merge user for a future date, subtract 7 calendar days from it (this is actually going to be a static value, and the formula doesn't have to account for weekends or holidays) and display the 2 different dates in 2 different locations in the document. I'm used to using {set} and {fillin} fields for some really elaborate merges, so I'm hoping this can serve as the basis to do what I need!!!

Mucho MUCHO thanks! I've been all over the MS newsgroups, but nothing I've found so far comes anywhere close to this!

[jscher2000]

Sorry, not an answer, just a little observation:

> if you use 07/07/2003 and -7 it will correctly return 30 Jun 2003.

This is the date I'd use in my stage demo. No matter how the computer interprets the date I entered (whatever its regional settings), it's always right! Thank you, thank you very much, right this way, bring your wallets...

[macropod]

Hi legalbear,

I'm unable to replicate this problem. When I enter 01/08/2003 and -7, the field returns 25 July 2003. It does this even if I just enter 01/08, with no year.

Since I can't replicate the problem, I can't tell you for sure what to do to fix it. However, if you change the first ASK field to:
{ASK StartDate "What is the starting date, in dd mmm yyyy format, please?" d {DATE @ "dd MMM yyyy"}}
that may resolve the issue by changing the way the day & month combo are entered/stored.

As for changing the date formats, you'd need to make the change in three places. For example, to get:
"If the starting date is Jul, 3 2003 and the offset is -7 days, then the new date is Jun, 26 2003."
you'd change the ASk field to:
{ASK StartDate "What is the starting date, in mmm, dd yyyy format, please?" d {DATE @ "MMM, dd yyyy"}}
change the {StartDate @ "d MMMM yyyy"}

[legalbear2001]

Dude, you are SOOOOOOOOOOO cool - I took your suggestions - they pointed me in a coupla different directions to give me EXACTLY what I need!! I've attached a .zip of a Word document with the revised code in it for ya. Now, it only outputs the two desired dates, and uses a static 7-day period

Mucho Mucho Thanks!!!

Attached file(s)

•  CodeforGeneratingRespond-Bydate.zip (4.01K)
  Number of downloads: 276

[macropod]

I see you've got the hang of it - but you might want to change the question in the ASK field to make it more relevant.

Cheers

[BillCoan]

Thanks for posting this, macropod. Nice job. This should be very useful for anyone who needs to do date calculations in a document.

[vswearingen]

Fabulous! Now can you make Word properly indicate a.m. and p.m. instead of the typographically incorrect AM and PM which seems to be endemic in Office? The rule is, paraphrasing the Chicago Manual of Style, "Lowercase a.m. and p.m. with periods, or the more traditional small capitals, " AM and PM ""

[fburg]

macropod,

The first time I saw this back in May 2003, I was very impressed as I should be.

Now I'm very confused.

The thread shows a post # back in the May timeframe and lots of people responding back then. Time goes by and there are some responses in July. Then again time goes by and now we have a Dec posting (a 2nd with mine here). But the date on your post is Dec 7, 2003 while the post # is back in May. At least that's what I'm seeing and l looked eleventeen times.

I had saved your post back then. I saved the one attached to the Dec 7 post. I compared them. They are different. For example, very first line has Tips & Tricks now while it didn't in May. Other differences too.

So the Dec 7 posting is an edited version of your May posting but doesn't show any edited on notation? Did it incorporate the July discussion with LegalBear on his/her problem? Did you edit your post from May to change the attachment?

Thanks.

Fred

[Slinky]

When you edit one of your own posts, you have the option to mark it as edited or not. The default option is No.

Macropod has modified the attachment several times already; he marks the latest additions with *NEW* in the body of the post.

Attached image(s)

• 

[fburg]

HansV,

Thanks for the quick answer. That's almost what I thought (other than not knowing you had an option whether to mark an edit). I did see the new down at the bottom but wasn't sure if this was new now or new another time. (Of course, I'd disagree with the default of not marking an edit but that's a Lounge Matter.)

I also saw in the thread that he didn't seem to have problems, nor did Jefferson, on the Interactive Date calc. So I was also asking if anything was added to this new item to reflect any of that discussion, which looked like it was in July. Do you know?

Fred

[jscher2000]

Fred, my post was actually, sort of, a joke. (Maybe not very funny, but I must have been seeing some vendor demos around that time.) Please ignore my earlier comment for purposes of determining whether the problem is/was/has been solved.

[macropod]

Hi Fred,

As you now know, I've been updated this progressively since the original post. Each time, I've highlighted any new examples with *NEW*. I've also been changing the version # on the post title, which now stands at 1.60.

Cheers
PS: No change was required as a result of the discussion on the Interactive Date calc.

[fburg]

Jefferson,

you thought I took you seriously?

Actually, I wasn't sure that your response over the summer was of direct response to the questions I was concerned with, so I skipped it (read that as a nice way of saying ignored). But thanks for responding.

Fred

[fburg]

hi macropod,

I did notice lots of version numbers in the thread (1.00, 1.01, 1.1 and now 1.6). I did also notice the *NEW in the listing of topics. This is good stuff. I'll probably show it off at my Word Workshop later in the month. You've saved me from planning half a lesson - at least. Thanks.

Fred

[macropod]

What, you didn't like the am/pm solution I gave in the penultimate example? OK, I didn't have a.m./p.m. (i.e. with the periods), but at least I had the lowercase representation - and the periods are there now if you want to see them.

Cheers

[vswearingen]

I am truly impressed Macropod. Who says you can't teach a new dog old tricks...or, in this case, typography. Thanks again. Handy calculations to add to my toolkit!

[AlanMiller]

Hi Macropod

This looks great, and obviously lots of work done on your part. Thank you.
Are you looking for any further "contributions"? I have a few date-related bits & pieces I've compiled over the years. Those that come to mind include: