Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Nov 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Date calculations in Word

    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!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tveglahn,

    Press Ctrl+F9
    In between the brackets type: Date+14
    WordDateFormula.JPG
    Click the Update button
    WordDateResult.JPG

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

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Nov 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the help, but that only is returning todays date, not Dec 4th.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Sorry,

    When you're retired one day is like the next!
    ROTFLOL.gif

    I'll get back on it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:
    WordDateFormula.JPG

    Result:
    WordDateResult.JPG

    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: VBA - Word - Calculated Date.docm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    tveglahn (2015-11-20)

  7. #6
    New Lounger
    Join Date
    Nov 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tveglahn,

    Could you post a test document with your current method?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Paul (macropod)'s fields work very well. They have been field-tested for years. Read through the tutorial again, especially the beginning.

    My web 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 with Paul's tutorial though. Typing the field in by hand can be a real bear and his are already in Word format.
    Charles Kyle Kenyon
    Madison, Wisconsin

  10. The Following User Says Thank You to Charles Kenyon For This Useful Post:

    tveglahn (2015-11-20)

  11. #9
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Quote Originally Posted by RetiredGeek View Post
    Tveglahn,

    Press Ctrl+F9
    In between the brackets type: Date+14
    WordDateFormula.JPG
    Click the Update button
    WordDateResult.JPG

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

    HTH
    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" }" } } } }" }
    Last edited by Charles Kenyon; 2015-11-20 at 13:46. Reason: add field codes
    Charles Kyle Kenyon
    Madison, Wisconsin

  12. The Following User Says Thank You to Charles Kenyon For This Useful Post:

    tveglahn (2015-11-20)

  13. #10
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Quote Originally Posted by RetiredGeek View Post
    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:
    WordDateFormula.JPG

    Result:
    WordDateResult.JPG

    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: VBA - Word - Calculated Date.docm

    HTH
    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.
    Last edited by Charles Kenyon; 2015-11-20 at 13:49.
    Charles Kyle Kenyon
    Madison, Wisconsin

  14. #11
    New Lounger
    Join Date
    Nov 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Picture2.jpgPicture1.pngI'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.

  15. #12
    New Lounger
    Join Date
    Nov 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Think I have it

    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*
    working code.pngworking results.png

    I appreciate all the help and responses!

  16. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by tveglahn View Post
    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
    [MS MVP - Word]

  18. The Following User Says Thank You to macropod For This Useful Post:

    Charles Kenyon (2015-11-22)

  19. #15
    New Lounger
    Join Date
    Nov 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •