Date calculations in Word

Topic

New Reply

Hello. Using code found on this site by macropod, I have been able to do almost everything I need. however, I am stuck in one thing. The code to display a calculated date n days away, works perfectly when I need something more than 23 days out, but whenever I select a range less than 23 days I am getting seemingly random months and always the 12th. I need it to display a date 14 days from the date the document is open. Anyone have a fix for this? Thank you!

Reply | Quote

Replies

Tveglahn,

Press Ctrl+F9
In between the brackets type: Date+14
42703-WordDateFormula
Click the Update button
42702-WordDateResult

You can use Alt+F9 to toggle between seeing the formula and seeing the result.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Tveglahn,

Press Ctrl+F9
In between the brackets type: Date+14
42703-WordDateFormula
Click the Update button
42702-WordDateResult

You can use Alt+F9 to toggle between seeing the formula and seeing the result.

HTH :cheers:

Nope. Field maths for dates are much more complex than this. The calendar based on Roman Emporers and moon cycles is not designed for simple calculations. See my page or Paul’s tutorial.

Here is a field that gives you the date 14 days in advance based on the CREATEDATE.

Code:

 { QUOTE "{ SET " Delay" "14" }
{  SET "DaysInMonth" { IF { CreateDate @ "MM" }  2
{  = ROUND(30.575*{ CreateDate @ "MM" },0)-
ROUND(30.575*{ = { CreateDate @ "MM" } -1 },0) }
{  IF {  = MOD( { CreateDate @"yy" } , 4 ) } > 0 "28" "29" } } }
{ SET "NextMonth" {  IF {  CreateDate @ "MM" } = 12 "1/97" 
"{ = {  CreateDate @ "MM" } + 1 }/97 } }
{  IF {   = {  REF "Delay" } + { CreateDate @ "dd" } } <= { REF"DaysInMonth" }
{ CreateDate @ "MMMM { = {  REF "Delay" } + { CreateDate @ "dd" } }, yyyy"}{ QUOTE { NextMonth @ "MMMM" } 
{ = { REF "Delay" } + { CreateDate @ "dd" } - { REF"DaysInMonth" } }, 
{ IF { CreateDate @ "MM" }  12 { CreateDate @ "yyyy" }
{ CreateDate @ "{ = 1 + { CreateDate @ "yyyy" } # "xxxx" }" } } } }" } 

Reply | Quote

Thanks for the help, but that only is returning todays date, not Dec 4th.

Reply | Quote

Tveglahn,

Ok, here’s one that actually gets the right date but it’s a little complicated.

It requires a Macro placed in the ThisDocument module:

Code:

Option Explicit

Private Sub Document_Open()

     On Error GoTo JustSetValue
     
     ActiveDocument.Variables.Add Name:="TwoWeeks", _
        Value:=Format(DateAdd("d", 14, Now()), "mm/dd/yy")
        
     On Error GoTo 0
     
     GoTo GetOut
     
JustSetValue:

   ActiveDocument.Variables("TwoWeeks").Value = Format(DateAdd("d", 14, Now()), "mm/dd/yy")
       
GetOut:

End Sub  'Document_Open

Then in your document:
42704-WordDateFormula

Result:
42705-WordDateResult

Note: This will update everytime you open the document UNLESS you hold down the Ctrl key while opening the document. If you don’t want this behavior we’ll have to refine this some by calculating against something like the Document Creation Date.

Test File:

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Tveglahn,

Ok, here’s one that actually gets the right date but it’s a little complicated.

It requires a Macro placed in the ThisDocument module:

Code:

Option Explicit

Private Sub Document_Open()

     On Error GoTo JustSetValue
     
     ActiveDocument.Variables.Add Name:="TwoWeeks", _
        Value:=Format(DateAdd("d", 14, Now()), "mm/dd/yy")
        
     On Error GoTo 0
     
     GoTo GetOut
     
JustSetValue:

   ActiveDocument.Variables("TwoWeeks").Value = Format(DateAdd("d", 14, Now()), "mm/dd/yy")
       
GetOut:

End Sub  'Document_Open

Then in your document:
42704-WordDateFormula

Result:
42705-WordDateResult

Note: This will update everytime you open the document UNLESS you hold down the Ctrl key while opening the document. If you don’t want this behavior we’ll have to refine this some by calculating against something like the Document Creation Date.

Test File:

HTH :cheers:

For this, and many purposes, fields are superior to vba. Primarily because they operate in a macro-free environment. On the other hand, vba has much of the date calculation built into it, which the fields do not.

Reply | Quote

Sorry,

When you’re retired one day is like the next!
35623-ROTFLOL

I’ll get back on it. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you very much! That works great for now. If anyone can figure out how to do it without macros it would be a help, so I don’t have to teach an entire department to make sure to enable their macros. I can do 60 days without macros so far.

Reply | Quote

Tveglahn,

Could you post a test document with your current method? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Paul (macropod)’s fields work very well. They have been field-tested for years. Read through the tutorial again, especially the beginning.

Myweb page on calculated dates predates that tutorial. It has a field which is set for 14 days ahead but you would need to change the createdate field to a date field.

Code:

 { QUOTE "{ SET " Delay" "14" }
{  SET "DaysInMonth" { IF { CreateDate @ "MM" }  2
{  = ROUND(30.575*{ CreateDate @ "MM" },0)-
ROUND(30.575*{ = { CreateDate @ "MM" } -1 },0) }
{  IF {  = MOD( { CreateDate @"yy" } , 4 ) } > 0 "28" "29" } } }
{ SET "NextMonth" {  IF {  CreateDate @ "MM" } = 12 "1/97" 
"{ = {  CreateDate @ "MM" } + 1 }/97 } }
{  IF {   = {  REF "Delay" } + { CreateDate @ "dd" } } <= { REF"DaysInMonth" }
{ CreateDate @ "MMMM { = {  REF "Delay" } + { CreateDate @ "dd" } }, yyyy"}{ QUOTE { NextMonth @ "MMMM" } 
{ = { REF "Delay" } + { CreateDate @ "dd" } - { REF"DaysInMonth" } }, 
{ IF { CreateDate @ "MM" }  12 { CreateDate @ "yyyy" }
{ CreateDate @ "{ = 1 + { CreateDate @ "yyyy" } # "xxxx" }" } } } }" } 

I recommend sticking withPaul’s tutorial though. Typing the field in by hand can be a real bear and his are already in Word format.

Reply | Quote

42708-Picture242707-Picture1I’ve been using macropod’s (paul’s) code, and as I said, it works great for my need for a date 60 days out. but whenever I use it for only 14 days out I’m getting bad dates. Anything between 11 and 23 days is only returning the 12th of a seemingly random month. Less than 10 days or more than 23 days works fine. That’s why I started this, because I didn’t know if a workaround was needed…. Here is what I am using so far. Notice how the first date should be 14 days from today, but is showing april 12th instead.

Reply | Quote

not sure why it is taking 2 very different codes to do the same thing for different days… but the following code seems to work. Let me know if you see anything that im missing. I’ll know for sure tomorrow when I open the doc back up and see if they update for the new day *smile*
42709-working-code42710-working-results

I appreciate all the help and responses!

Reply | Quote

tveglahn,

I have one word for you…MAINTENANCE!

Make sure you document what you are doing line by line. Six months from now you may have trouble remembering what it all does, I’d probably have trouble tomorrow!

That is the advantage of the VBA much less complicated. Create a template with the VBA code and create documents from that and the user don’t have to worry about it. You could even write VBA code to insert the field codes at the cursor.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hello. Using code found on this site by macropod, I have been able to do almost everything I need. however, I am stuck in one thing. The code to display a calculated date n days away, works perfectly when I need something more than 23 days out, but whenever I select a range less than 23 days I am getting seemingly random months and always the 12th. I need it to display a date 14 days from the date the document is open. Anyone have a fix for this? Thank you!

Did you read the tutorial’s Introduction, especially the part to do with regional date settings?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I did… but since it was working with most days, didn’t think that was the problem. Now that you point it out and I tried that simple change…. I see that was my problem. Thank you!

Reply | Quote