Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 54
  1. #16
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 204 Times in 185 Posts

    Re: Date Calculation Discussions (1.00)

    Hi gogetter,

    I think the reason the output hadn't changed was that you hadn't updated the field, which you do by pressing F9 - it looks like you'd simply toggled the coding view off via Shift-F9 or Alt-F9.

    Had the field updated, it would have disappeared from view because of a number of editing errors. For example, you used standard braces instead of field braces (created via Ctrl-F9) in a number of places and the SET yy field has the INT expression on the wrong side of a set of field braces. You also deleted the '=' sign on the last line and replaced it with a pair of single quotes around the 'mm*10^6+dd*10^4+yy' expression. As with most programming languages, Word's field coding has an unforgiving set of syntax requirements, without which things fall over.

    I've added a correctly-coded version of the field to your document so that you can compare the two (and use the latter).

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  2. #17
    New Lounger
    Join Date
    Apr 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Macropod - This is AWESOME! Thank you so much. Also, thanks for the explanation, it really helped!

  3. #18
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Macropod
    I want to create a field in a work document that takes the current days date and adds 19 days to it. I have read your file and just confused myself!
    Hope you can help!

  4. #19
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Date Calculation Discussions (1.00)

    Welcome to Woody's Lounge.

    In macropod's document, look for the section labeled "Calculate a day, date, month and year, using n (301) days delay" (it's at the end of page 6 on my PC, but pagination might be slightly different on yours). This section contains a field that at the moment displays Tuesday, 17 January 2006 if you haven't updated the field, or Tuesday, 23 May 2006 (i.e. 301 days after today, 26 July 2005) if you have. Select this field, then right-click it and select Toggle Field Codes from the popup menu. You'll see

    {QUOTE
    {SET DELAY 301}
    {SET ...

    Change the 301 to 19, taking care not to change anything else. Then press F9 to hide the field codes and update the result, You should see Sunday, 14 August 2005.

    You can copy the field to the clipboard and paste it into your own document.

  5. #20
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Hans
    I think it has worked I just have to wait until tomorrow to see if it updates automatically on opening as I am on a networked pc and I can't change the date and time!
    H

  6. #21
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Date Calculation Discussions (1.00)

    The field probably won't update automatically, you'll have to click in it and press F9, or print the document (providing that 'Update fields' is checked in the Print tab of Tools | Options...) in order to update it.

  7. #22
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Hans
    I have forseen a slight problem, I have just tried this out on an non-networked pc where I can change the date and time etc, when I open the document it doesn't change the date, Ihave update field before printing selected which automatically will update but I dont want to have to go around everyones pc and make sure that this is checked is there anything that I can do that I can apply directly to the document so that when it opens on anyones pc this date will change according to todays date without having to update the field codes etc. I really want this systme to be idiot proof so people can literally just open the document and print it.
    h

  8. #23
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Date Calculation Discussions (1.00)

    You can create VBA code in your document that will update the field each time the document is opened:
    - Activate the Visual Basic Editor (Alt+F11)
    - Double click the ThisDocument item corresponding to your document in the explorer-like pane on the left (the Project Explorer)
    - Type or copy/paste the following code into the code window that appears:

    Private Sub Document_Open()
    ThisDocument.Fields.Update
    End Sub

    This code will not run if the user's macro security is set to High (in Tools | Macro | Security), unless you sign the code with a digital certificate. If macro security is set to Medium, the user will be have to click Yes to activate macros each time the document is opened. (Setting macro security to Low is not recommended)

  9. #24
    New Lounger
    Join Date
    Nov 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    An excellent document Macroprod. I've used several of these fields already.

    A quick question though: Is it possible to create a field that displays a future day and month (excluding the year) in U.S. format (i.e. mm/dd)? I've tried messing around with the formula and though I have come close, I can't quite seem to hit the answer.

    The "mask" doesn't seem to want to cooperate when I attempt to make it show only the month and day.

    Any help would be greatly appreciated!

    ~Joe

  10. #25
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 204 Times in 185 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Joe,

    Did you follow the instructions under the heading 'Date input/output formats' to take account of the differences in regional settings? If you've done that, all you should need to do to get the date to display in 'mm/dd' form is to format the final picture switch as 'MM/dd' (note the use of 'M' caps - 'm' is for minutes).

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #26
    New Lounger
    Join Date
    Jan 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Push dates to Weekday?

    MacroPod,

    Thanks for the excellent work; I've put several of your code samples here to good use.

    I have a new challenge and couldn't find this on in the document or on the forum. I'd like a way to push calculated dates that fall on a weekend to either the nearest weekday, (Sat gets pushed to Fri, Sun gets pushed to Mon), both back to Friday (Sat & Sun both get pushed to Fri), or both forward to Monday (Sat & Sun both get pushed to Mon). I can somewhat understand what is logically needed (use an if-then statement that adds or subtracts 1 or 2 to the Julian date?), but am not sure how to go about it. I'm sure I'm actually talking about 3 different bits of code here for the 3 different behaviours, so I'll be happy with whatever you feel inclined to consider. This is a "nice to have" and not a pressing need, so please don't trouble yourself to rush on this in any way. Again, I appreciate your impressive work and continuing assistance!

    Thanks,

    Dragonrand

  12. #27
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 204 Times in 185 Posts

    Re: Push dates to Weekday?

    Hi Dragonrand,

    To get one of the calculated dates to automatically return a weekday instead of a Saturday or Sunday in a field like any of:
    . 'Calculate a day, date, month and year, using n (301) days delay',
    . 'Interactively Calculate A Past Or Future Date'
    . 'Date Calculations In A Mailmerge'
    . 'Date Calculations in a Table'
    you could add a new line of code right after the line:
    {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}

    To return Fridays instead of Saturdays, and Mondays instead of Sundays, the code to add is:
    {SET jd {=jd+IF(MOD(jd,7)=5,-1,IF(MOD(jd,7)=6,1,0))}}
    To return Fridays instead of Saturdays or Sundays, the code to add is:
    {SET jd {=jd+IF(MOD(jd,7)=5,-1,IF(MOD(jd,7)=6,-2,0))}}
    To return Mondays instead of Saturdays or Sundays, the code to add is:
    {SET jd {=jd+IF(MOD(jd,7)=5,2,IF(MOD(jd,7)=6,1,0))}}

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #28
    New Lounger
    Join Date
    Feb 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Macropod
    Thankyou for your suggestions in the other posting spot.
    I am the one who is having trouble with creating a table that will calculate the day a client is referred to todays date. I was trying for having the calculation being the number of days client is waiting for service. I cannot seem to duplicate much of what you are suggesting. Here is a copy of the table I have started.
    Attached Files Attached Files

  14. #29
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 204 Times in 185 Posts

    Re: Date Calculation Discussions (1.00)

    Hi NeedsDirection,

    Welcome to the Lounge!

    Attached is a copy of your document, with calculation fields inserted into the first 3 rows of your 'Age' and '#days waiting' columns. As well, there are some calculation fields referenced by these hiding in your 'Date filled out' cell.

    Presently, the document is 'protected for forms', so that you can play with the fields and see what they do. I've already filled out 'Date filled out', formfield and the first two 'Birth Date' and 'Date Referred' formfields , but you can change these entries. To see the field coding, and extend it down the table, you'll need to unprotect the document. The easiest way to display the field coding is to toggle the display via Alt-F9.

    Since I didn't know how much of your clients' ages you might want to include in the output, I've provided for years, months and days. You can edit out whatever parts you don't need. Likewise, I've formatted the 'Date filled out', 'Birth Date' and 'Date Referred' formfield dates to display in 'dd MMM yyyy' format. You might want to change this. To do so, right-click on the field concerned and select 'properties'. From there you've got access to the formfield attributes.

    Note that the 'Birth Date' and 'Date Referred' formfields set numbered 'BirthDate' and 'RefDate' bookmarks, corresponding with the client's row number. To use the solution I've provided further down the table, you'll need to continue this approach. The calculation fields in the 'Age' and '#days waiting' columns refer to these numbered bookmarks and you'll likewise need maintain that behaviour when using them further down the table.

    With the 'Age' calculation fields, you'd change the values in red, below, to match the client row number:
    {QUOTE
    {SET by {Birthdate<font color=red>3</font color=red> @ yyyy}}
    {SET bm {Birthdate<font color=red>3</font color=red> @ M}}
    {SET bd {Birthdate<font color=red>3</font color=red> @ d}}
    {SET md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400 )=0)-(MOD(yy,100)=0)),31-((mm=4)+(mm=6)+(mm=9)+(mm=11)))}}
    {Set Years1{=yy-by-(mm<bm)-(mm=bm)*(dd<bd)}}
    {Set Months1{=MOD(12+mm-bm-(dd<bd),12)}}
    {Set Days1{=MOD(md+dd-bd,md) # 0}}
    {IF{Birthdate<font color=red>3</font color=red>}= " " "" "{Years1 # 0}<font color=blue>Y,{Months1 # 0}M, {Days1 # 0}D</font color=blue>"}}
    and you could delete as much of the blue portion as you like.

    With the '#days waiting' fields, you'd likewise change the values in red, below, to match the client row number:
    {IF{ RefDate<font color=red>3</font color=red>}= " " "" {=
    {SET a{=INT((14-{mm})/12)}}
    {SET b{={yy}+4800-a}}
    {SET c{={mm}+12*a-3}}
    {SET d{dd}}
    {=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
    -
    {SET a{=INT((14-{RefDate<font color=red>3</font color=red> @ M})/12)}}
    {SET b{={RefDate<font color=red>3</font color=red> @ yyyy}+4800-a}}
    {SET c{={RefDate<font color=red>3</font color=red> @ M}+12*a-3}}
    {SET d{RefDate<font color=red>3</font color=red> @ d}}
    {=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045} # ,0;;0}}

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  15. #30
    New Lounger
    Join Date
    Feb 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    I can't find a post that shows me how to add a date that is 31 days after today. Any help?

Page 2 of 4 FirstFirst 1234 LastLast

Posting Permissions

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