Calculate Difference Between Two Dates (MacroPod)
-
- PlutoniumLounger
- Posts: 15621
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Calculate Difference Between Two Dates (MacroPod)
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 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:- 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"} 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
I am trying to calculate the difference between two dates.
MacroPod's excellent "DateCalc V2_85.doc" gets me started on page 10 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:- 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"} 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.
There's nothing heavier than an empty water bottle
-
- GoldLounger
- Posts: 2599
- Joined: 24 Jan 2010, 15:26
- Location: Olympia, WA
Re: Calculate Difference Between Two Dates (MacroPod)
Why not just use something like the following ?
https://www.timeanddate.com/date/duration.html
https://www.calculator.net/date-calculator.html
https://www.thecalculator.co/time/Calen ... r-286.html
https://support.microsoft.com/en-us/off ... 100a162f38
https://www.timeanddate.com/date/duration.html
https://www.calculator.net/date-calculator.html
https://www.thecalculator.co/time/Calen ... r-286.html
https://support.microsoft.com/en-us/off ... 100a162f38
I am so far behind, I think I am First
Genealogy....confusing the dead and annoying the living
Genealogy....confusing the dead and annoying the living
-
- 5StarLounger
- Posts: 617
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Calculate Difference Between Two Dates (MacroPod)
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.
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
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.
If you want the days between two dates, you need two dates, not one. So, you will need at least the StartDate.I have tried to modify the code to a single stand-alone field
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.
Attorney Charles Kenyon
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- PlutoniumLounger
- Posts: 15621
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Calculate Difference Between Two Dates (MacroPod)
Ah! I love these easy questions!
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.
There's nothing heavier than an empty water bottle
-
- PlutoniumLounger
- Posts: 15621
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Calculate Difference Between Two Dates (MacroPod)
Thanks Charles.Charles Kenyon wrote: ↑19 Nov 2022, 18:58I 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.
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.
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.If you want the days between two dates, you need two dates, not one. So, you will need at least the StartDate.
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
There's nothing heavier than an empty water bottle
-
- 5StarLounger
- Posts: 617
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Calculate Difference Between Two Dates (MacroPod)
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?
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?
Attorney Charles Kenyon
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- PlutoniumLounger
- Posts: 15621
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Calculate Difference Between Two Dates (MacroPod)
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!
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.Did you try my suggestion getting rid of one layer of the picture switch for the date?
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.
, as always, for prodding me into action.
Chris
There's nothing heavier than an empty water bottle
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: Calculate Difference Between Two Dates (MacroPod)
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
-
- 5StarLounger
- Posts: 617
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Calculate Difference Between Two Dates (MacroPod)
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
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.
Attorney Charles Kenyon
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: Calculate Difference Between Two Dates (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:
Of course, if your document's CREATEDATE value is February 4, 2019, you could use just:
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}}
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]
[Fmr MS MVP - Word]
-
- PlutoniumLounger
- Posts: 15621
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Calculate Difference Between Two Dates (MacroPod)
Paul, in a word "BRILLIANT"
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.
There's nothing heavier than an empty water bottle
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: Calculate Difference Between Two Dates (MacroPod)
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.ChrisGreaves wrote: ↑21 Nov 2022, 10:03I 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?
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]
-
- PlutoniumLounger
- Posts: 15621
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Calculate Difference Between Two Dates (MacroPod)
Oh Great.
Just Great!
Three more homework exercises downloaded (mutter, mutter, mutter) ...
I now have DateCalc V2_86.doc 4/29/2013
Thanks Paul.
There's nothing heavier than an empty water bottle
-
- 5StarLounger
- Posts: 617
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Calculate Difference Between Two Dates (MacroPod)
These remain my go-to resources. Thank you again.macropod wrote: ↑21 Nov 2022, 12:26I 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.ChrisGreaves wrote: ↑21 Nov 2022, 10:03I 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?
They may not have been updated in years, but AFAIK, Word fields have not changed in years, either.
Attorney Charles Kenyon
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: Calculate Difference Between Two Dates (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]
[Fmr MS MVP - Word]
-
- 5StarLounger
- Posts: 617
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Calculate Difference Between Two Dates (MacroPod)
Anything added beside the Insert and merge bar code fields?
Attorney Charles Kenyon
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: Calculate Difference Between Two Dates (MacroPod)
The CITATION & BIBLIOGRAPHY fields immediately come to mind.
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]