Page 1 of 4 123 ... LastLast
Results 1 to 15 of 54
  1. #1
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts

    Date Calculation Discussions (1.00)

    This thread has been created for the purpose of continuing the Date Calculation discussions started in the Star Post thread at <post#=249902>post 249902</post#>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Dec 2004
    Location
    The Okanagan, Br. Columbia, Canada
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Phil,

    I still have not gotten it yet as I did what you suggested but now the date is showing as May 12, 2004 even though my clock on Windows is showing the correct date for today (12/4/04).

    As well their were four instances of the word date so I changed each individually and even all four but that still did not fix it.

    I've attached a PDF file I created showing what I have for the field data.

    Thanks again...

    Curtis
    Attached Files Attached Files

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Curtis,

    The problem you're now having is because you're using a US date format, whereas the document uses non-US date formatting. This issue is discussed in the intro. To get the result you want, change:
    'dd*10^6+mm'
    on the last line of the field to:
    'mm*10^6+dd'

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #4
    Lounger
    Join Date
    Nov 2004
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    (Edited by HansV to make provide link to post - see <!help=19>Help 19<!/help>)

    Hi Macropod

    Thanks for the help with date calculation - <post#=431960>post 431960</post#>

    I am trying to format the output of the forward date calculated in Post 431960 in the form e.g. 31st June 2005 but with the 'st' in superscript. I can't see how I can combine the code in that section of your download with the code created to generate the date and generate the printed output.

    Please can you help

    Best regards

  6. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Duncan,

    Change the last line from:
    {=mm*10^6+dd*10^4+yy # "00'-'00'-'0000"} @ "dddd, MMMM d yyyy"}
    (I'm guessing that you're using a 'dddd, MMMM d yyyy' output format) to:
    {=mm*10^6+dd*10^4+yy # "00'-'00'-'0000"} @ "dddd, MMMM d
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #6
    Lounger
    Join Date
    Nov 2004
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Macropod

    Thank you again - Contrary to how it may seem, I am actually doing other bits under my own steam thanks to your help.

    Best regards

  8. #7
    Lounger
    Join Date
    Nov 2004
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Macropod

    I've been testing the date calculations at extremes again before releasing the document and there seems to be a further problem with February.
    If 6 months is added to Aug 29th the answer is 29-02-2005. Word formating recognises the problem and it does not display properly. The same is true for other years except leap years - when Feb 29th does exist. It is not August specific and 28th Feb is displayed correctly when the 30th and 31st of the month are entered. Also on leap year if 31st of Aug 04 has 42 months added the date reverts to 29th Feb 08 which is correct.
    Have you any suggestions?

    Thanks and regards
    Duncan

  9. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Duncan,

    Sorry 'bout that. The '>29' in the 'SET dd' expression should have been '>28' <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #9
    Lounger
    Join Date
    Nov 2004
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Macropod

    Have now done wonders in docs thanks to your date help

    Am not sure if the attachment is of any use to anyone or if I reinvented a wheel.

    The * Cadtext switch does not properly create a printed output for money as an example. I crated the attachment that seems to work and wondered if it might be of use to others and if perhaps you wanted to check it and let others see it?

    Regards

    Duncan
    Attached Files Attached Files

  11. #10
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Madison, Wisconsin, Wisconsin, USA
    Posts
    1,672
    Thanks
    49
    Thanked 61 Times in 59 Posts

    Re: Date Calculation Discussions (1.00)

    Hi,

    First, I think you mean * Cardtext.

    Second, have you tried * DollarText?
    Charles Kyle Kenyon
    Madison, Wisconsin

  12. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Duncan,

    Your field can be both simplified and extended, as per second example in the attached in which I've implemented a solution using a FILLIN field that you could easily replace with your mergefield. This will deal with pounds to 9 digits and pennies, both as positive and negative values.

    For more on Word field maths and numeric picture switches, see <post#=365442>post 365442</post#>.

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Duncan,

    The code in the Date Calc document (see <post#=249902>post 249902</post#>) has now been updated, with a new and simpler algorithm for applying the 'February' test. The relevant code portion is now:
    {SET dd{=IF(({DATE @ d}>28)*(mm=2)=1,28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+( {DATE @ d}>30)>1,30,{DATE @ d}))}}

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #13
    New Lounger
    Join Date
    Apr 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    I'm looking to calculation the last day of the previsous month with a "MMMM d yyyy" format. Please help.

  15. #14
    New Lounger
    Join Date
    Apr 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation Discussions (1.00)

    Hi Macropod -- Thanks for the quick reply. Here's what I have and it still comes up as dddd, d MMMM yyyy. Please help. Looking for MMMM dd yyyy. Thanks a bunch.
    Attached Files Attached Files

  16. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts

    Re: Date Calculation Discussions (1.00)

    <P ID="edit" class=small>(Edited by macropod on 26-Apr-05 08:37. Corrected INT syntax error in SET yy field.)</P>Hi gogetter,

    You can do this by modifying the field found under 'Calculate the day & date of the last or nth-to-last day of this month' in the DateCalc document (<post#=249902>post 249902</post#>). The steps are as follows:
    . After the embedded field '{SET Subtract 0}', insert a new field coded as '{SET MAdjust -1}'
    . Change the '{SET yy{DATE @ yyyy}}' field to '{SET yy{={DATE @ yyyy}+INT(({DATE @ M}+MAdjust)/12)}}'
    . Change the '{SET mm{DATE @ M}}' field to {SET mm{=MOD({DATE @ M}+MAdjust-1,12)+1}}
    . Change the 'dd*10^6+mm*10^4+yy' string to 'mm*10^6+dd*10^4+yy'
    . Change the '"dddd, d
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 4 123 ... 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
  •