Calculate Difference Between Two Dates (MacroPod)

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Calculate Difference Between Two Dates (MacroPod)

Post by ChrisGreaves »

Hello Paul, or anyone else who knows the answer.

I am trying to calculate the difference between two dates.
MacroPod's excellent "DateCalc V2_85.doc" gets me started on page 10
Untitled.png
This code (as supplied) works, but is dependant on two bookmarked fields [startdate] and [enddate].

I have tried to modify the code to a single stand-alone field, but there is something I do not understand about field codes in general.

In the calculation shown above, EndDate is a reference to a bookmark where we expect to find today's date:-
Untitled2.png
I reason that I should be able to replace every occurrence of the bookmark identifier EndDate with the field code for today's date, to wit {DATE \@ "d MMM. yy"}
Untitled3.png
I replace the first occurence of EndDate with the field {DATE \@ "d MMM. yy" } and the calculation delivers the correct result (1,384).
Encouraged by this, I replace the second occurrence of EndDate with the same field {DATE \@ "d MMM. yy" }, but now the result shows a staggering -249,300.

Clearly I am out of my depth.

Question1: For fields in general, should I be able to replace one field or portion of a calculation field with another valid field (as I did here on the first and second occurrences of EndDate)?

Question 2: If not, why not?

Question 3: Why did my specific example of a modified calculation field change?

I am of course interested in a solution to my specific problem of calculating a value as a single field, using no auxiliary data from the document, but more than that, I would like to resolve the basic flaw in my understanding of {field codes} as LEGO-like blocks that can be replaced.
Thanks, Chris
PS Paul, my DateCalc.zip is neatly timestamped 2009/12/12. Should I obtain a more recent version? (I think my copy stems from about 1885!)
C
You do not have the required permissions to view the files attached to this post.
Last edited by ChrisGreaves on 21 Nov 2022, 09:49, edited 1 time in total.
An expensive day out: Wallet and Grimace

User avatar
DaveA
GoldLounger
Posts: 2599
Joined: 24 Jan 2010, 15:26
Location: Olympia, WA

Re: Calculate Difference Between Two Dates (MacroPod)

Post by DaveA »

I am so far behind, I think I am First :evilgrin:
Genealogy....confusing the dead and annoying the living

User avatar
Charles Kenyon
4StarLounger
Posts: 596
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Calculate Difference Between Two Dates (MacroPod)

Post by Charles Kenyon »

Hi Chris,

I think most of us are out of our depth when dealing with these complex fields.
Paul is pretty much in a class by himself. He regularly visits this forum and so is likely to be giving you an Answer. All I can do is speculate.

When dealing with complex fields, I've found that breaking out the component parts to see what they produce is helpful.
Have you tried just {{ DATE \@ "dd MM yyyy" } \@ M } in your document to see if it produces "11?"

Date calculations in vba or Excel are far simpler. I blame it on our antiquated calendar system, but these other softwares deal with that.
I have tried to modify the code to a single stand-alone field
If you want the days between two dates, you need two dates, not one. So, you will need at least the StartDate.

Having a separate place for the StartDate and EndDate in bookmarks lets you use a different date than today's date easily. You can then simply type dates in the bookmarks if needed to use arbitrary dates. This makes Paul's field far more versatile than simply inserting today's date.

Did you read the introductory material and make sure you are on the correct regional date system?

The other uses of EndDate in this field have pictures and you seem to be trying to double up those pictures. I do not know that this works. Using the bookmark gives you a text string to which a picture switch is applied. Perhaps using the DATE field also does this. I do not know.

I think you would want simply { DATE \@ M } rather than {{ DATE \@ "dd MM yyyy" } \@ M }.


Also, it is possible you need spaces in those fields. Again, I am not Paul.

https://addbalance.com/word/datefields1.htm#PageStart
http://www.gmayor.com/formatting_word_fields.htm
Last edited by Charles Kenyon on 19 Nov 2022, 19:09, edited 2 times in total.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Calculate Difference Between Two Dates (MacroPod)

Post by ChrisGreaves »

DaveA wrote:
19 Nov 2022, 16:51
Why not just use something like the following ?
Ah! I love these easy questions! :grin:
Because (a) I don't want to calculate the difference in dates, I want my MSWord document to calculate the difference every time I open the document and (b) It is an MSWord document, and the datediff function is, AFAIK, in MSExcel!
Cheers, Chris
PS (c) I keep promising myself that I will spend a week, or a month, maybe devote a year to understanding what was going on in MacroPod's brain when he composed his wonderful reference document! C.
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Calculate Difference Between Two Dates (MacroPod)

Post by ChrisGreaves »

Charles Kenyon wrote:
19 Nov 2022, 18:58
I think most of us are out of our depth when dealing with these complex fields.
Paul is pretty much in a class by himself. He regularly visits this forum and so is likely to be giving you an Answer. All I can do is speculate.
Thanks Charles.
I agree. As in my reply to Dave, I suspect I really would need the best part of a month to become familiar with Paul's document.
If you want the days between two dates, you need two dates, not one. So, you will need at least the StartDate.
Correct! In my specific case, the start date is a fixed date - February 4th 2019, and the end date is dynamic NOW() or DATE(), so I thought I would try to simplify an existing calculation.
Instead of calculating the date difference between to stored chunks of user-text (as in an Invoice or Statement, for example), I reasoned that the date calculation could be a {field} that did the entire calculation in its head, in a manner of speaking.
Cheers, Chris
An expensive day out: Wallet and Grimace

User avatar
Charles Kenyon
4StarLounger
Posts: 596
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Calculate Difference Between Two Dates (MacroPod)

Post by Charles Kenyon »

Chris,

Did you read the introductory material in the tutorial?
Did you try my suggestion getting rid of one layer of the picture switch for the date?

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Calculate Difference Between Two Dates (MacroPod)

Post by ChrisGreaves »

Charles Kenyon wrote:
20 Nov 2022, 04:14
Did you read the introductory material in the tutorial?
Hi Charles, yes I did. The first time I read it, shortly after Saturday, March 24, 2007 7:41:00 AM, I understood very little.
I have not re-read it every time I've dipped into DateCalc V2_85.doc, but at your prompting, I am re-reading it now.

The introductory text alone warrants several days examination, IMHO.
For example (page 4) "The DATE field inserts the current date into your document, and updates every time you open the document or perform an action that causes the document fields to update.". If true, this would make the DATE field different from most other fields. I believe that the simple {SEQ} must be updated by a macro when the document is opened; it will not update automatically. I could be wrong, but determining that would be part of my time spent exploring just the Introduction!
Did you try my suggestion getting rid of one layer of the picture switch for the date?
Not yet. This will make for a standalone exercise Real Soon Now that we have a week of rain/pellets/flurries/snow upon us and, sigh, I shall have to sit in a warm room with mugs of hot tea and cinnamon cookies, music playing softly on the jukebox.

I took the date "Saturday, March 24, 2007 7:41:00 AM" from the File, Properties, Statistics panel. This magnus opus of Macropod looks to be at least fifteen years old. (Later) I checked Saturday, December 12, 2009 11:59:58 PM, and I am still using Word2003, so I think that I am within constraints of version. Also I checked {SEQ} fields - mine are not updated when i open a file, which is why (sigh) I updatre all fields before Saving a document.

:thankyou: , as always, for prodding me into action.
Chris
An expensive day out: Wallet and Grimace

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Calculate Difference Between Two Dates (MacroPod)

Post by SpeakEasy »

Erm .. given your requirement, could you not just use a macro like this one (and yes, DateDiff works just fine in Word):

Code: Select all

Public Sub AutoOpen()
    ' Assumes a bookmark called "DaysDifference" exists somewhere in your document
    ActiveDocument.Bookmarks("DaysDifference").Range.Text = DateDiff("d", #2/4/2019#, Now())
End Sub

User avatar
Charles Kenyon
4StarLounger
Posts: 596
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Calculate Difference Between Two Dates (MacroPod)

Post by Charles Kenyon »

Hi Chris,

I believe that DateCalc has been updated since 2007 but may be mistaken. The version I have is DateCalc V2_86. It is still distributed in .doc format.

Date fields are different. They do update upon opening. That is part of the reason I seldom use them.
Here is my page on the date fields available.
https://addbalance.com/word/datefields1.htm#PageStart
Last edited by Charles Kenyon on 21 Nov 2022, 22:13, edited 1 time in total.

User avatar
macropod
4StarLounger
Posts: 508
Joined: 17 Dec 2010, 03:14

Re: Calculate Difference Between Two Dates (MacroPod)

Post by macropod »

All you need do is:
1. Wrap the existing field code in a QUOTE field;
2. Insert a SET field to define the StartDate bookmark's value; and
3. Change the 'EndDate' references to 'DATE'.

What you should end up with is:

Code: Select all

{QUOTE {SET StartDate "February 4, 2019"}{=
{SET a{=INT((14-{DATE \@ M})/12)}}
{SET b{={DATE \@ yyyy}+4800-a}}
{SET c{={DATE \@ M}+12*a-3}}
{SET d{DATE \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
-
{SET a{=INT((14-{StartDate \@ M})/12)}}
{SET b{={StartDate \@ yyyy}+4800-a}}
{SET c{={StartDate \@ M}+12*a-3}}
{SET d{StartDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
\# ,0}}
Of course, if your document's CREATEDATE value is February 4, 2019, you could use just:

Code: Select all

{=
{SET a{=INT((14-{DATE \@ M})/12)}}
{SET b{={DATE \@ yyyy}+4800-a}}
{SET c{={DATE \@ M}+12*a-3}}
{SET d{DATE \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
-
{SET a{=INT((14-{CREATEDATE \@ M})/12)}}
{SET b{={CREATEDATE \@ yyyy}+4800-a}}
{SET c{={CREATEDATE \@ M}+12*a-3}}
{SET d{CREATEDATE \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
\# ,0}
Paul Edstein
[Fmr MS MVP - Word]

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Calculate Difference Between Two Dates (MacroPod)

Post by ChrisGreaves »

macropod wrote:
20 Nov 2022, 21:58
All you need do is:
1. Wrap the existing field code in a QUOTE field;
2. Insert a SET field to define the StartDate bookmark's value; and
3. Change the 'EndDate' references to 'DATE'.
Paul, in a word "BRILLIANT" :clapping: :clapping: :clapping:
I took a fresh copy of the formula from DateCalc.doc, made the three changes you suggested and IT WORKED!
Acceptance tests:-
(1) I don't trust myself, so I made the equivalent calculation in Excel2003 (NOW()-1386) and received Feb 4, 2019
(2) I still don't trust myself, so I copied the modified {field} into a new Word2003 document and received 1386.
Now I will have daily confirmation of what I believe is the record (among living inhabitants) of the longest consecutive number of nights asleep in Bonavista.
Small comfort for a small soul!

Charles is partially right when he says "Paul is pretty much in a class by himself." You are in a class by yourself with two vacant classes below you to the next inhabited class. To make matters worse, I suspect that you have not been standing still for the past, what, twenty years since you began this work with, not {fields}, but {date fields}

However, I am nothing if not selfish and greedy; Might you, on your next comet-like orbit, post here a link to the most up-to-date version of your DateCalc .zip?

It is bad enough that I am still using Word2003, but asking you questions about an out-of-date version of DateCalc is presuming on your eternal kindness.

Thanks 1,386 times
Chris
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
macropod
4StarLounger
Posts: 508
Joined: 17 Dec 2010, 03:14

Re: Calculate Difference Between Two Dates (MacroPod)

Post by macropod »

ChrisGreaves wrote:
21 Nov 2022, 10:03
I am nothing if not selfish and greedy; Might you, on your next comet-like orbit, post here a link to the most up-to-date version of your DateCalc .zip?
I suggest you check out the 'Sticky' threads at the top of each of the forums under: https://www.msofficeforums.com/microsoft-word/. There you'll find a dozen threads containing the latest versions of the various tutorials (which I haven't updated for some years) plus a bunch of other useful stuff I've written on Word-related topics.
Paul Edstein
[Fmr MS MVP - Word]

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Calculate Difference Between Two Dates (MacroPod)

Post by ChrisGreaves »

macropod wrote:
21 Nov 2022, 12:26
... the latest versions of the various tutorials (which I haven't updated for some years) plus ...
Oh Great.
Just Great!
Three more homework exercises downloaded (mutter, mutter, mutter) ... :grin:

I now have DateCalc V2_86.doc 4/29/2013

Thanks Paul.
An expensive day out: Wallet and Grimace

User avatar
Charles Kenyon
4StarLounger
Posts: 596
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Calculate Difference Between Two Dates (MacroPod)

Post by Charles Kenyon »

macropod wrote:
21 Nov 2022, 12:26
ChrisGreaves wrote:
21 Nov 2022, 10:03
I am nothing if not selfish and greedy; Might you, on your next comet-like orbit, post here a link to the most up-to-date version of your DateCalc .zip?
I suggest you check out the 'Sticky' threads at the top of each of the forums under: https://www.msofficeforums.com/microsoft-word/. There you'll find a dozen threads containing the latest versions of the various tutorials (which I haven't updated for some years) plus a bunch of other useful stuff I've written on Word-related topics.
These remain my go-to resources. Thank you again.

They may not have been updated in years, but AFAIK, Word fields have not changed in years, either.

User avatar
macropod
4StarLounger
Posts: 508
Joined: 17 Dec 2010, 03:14

Re: Calculate Difference Between Two Dates (MacroPod)

Post by macropod »

MS has added a few new fields since then, but nothing that affects anything in those tutorials. The way some fields work has changed over time though, but the solutions in the tutorials seem to work in all Word versions.
Paul Edstein
[Fmr MS MVP - Word]

User avatar
Charles Kenyon
4StarLounger
Posts: 596
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Calculate Difference Between Two Dates (MacroPod)

Post by Charles Kenyon »

Anything added beside the Insert and merge bar code fields?

User avatar
macropod
4StarLounger
Posts: 508
Joined: 17 Dec 2010, 03:14

Re: Calculate Difference Between Two Dates (MacroPod)

Post by macropod »

The CITATION & BIBLIOGRAPHY fields immediately come to mind.
Paul Edstein
[Fmr MS MVP - Word]