News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Calculated date merge field issue

    Posted on truesupport Comment on the AskWoody Lounge

    Home Forums AskWoody support Microsoft Office by version Office 365 and Click-to-Run Calculated date merge field issue

    Tagged: 

    Viewing 4 reply threads
    • Author
      Posts
      • #2171331 Reply
        truesupport
        AskWoody Lounger

        Hi everyone,

        I used the brilliant date calc tutorial document to display the day after a merge date field but I can’t get it to work properly. It looks like the month and day are being swapped and then a day added in some cases, and then with others, the month and day are swapped but a day is not added.

        I’ve attached the formula and the results.

        Please have a look and let me know what I’ve done wrong.

        Thanks,
        Audrie

        Attachments:
      • #2171348 Reply
        Kirsty
        Da Boss

        Hi Audrie, Welcome to AskWoody!

        Could you please post your formula, paste the copied text (rather than a screenshot), select the text, then click CODE on the Text editor tab? That will help people to try out your formula as simply as possible 🙂

        I’m intrigued how the June start date gave a June follow-up date, but the February and December ones also had June follow-up dates… peculiar!

        • #2171719 Reply
          truesupport
          AskWoody Lounger

          Thanks, Kirsty!

          Unfortunately, I can’t paste the formula into this editor, it only pastes the result.  So I’ve saved the formula to a blank document and attached it here.

          Thanks in advance for having a look.

          Audrie

           

          Attachments:
          • #2172142 Reply
            Kirsty
            Da Boss

            Unfortunately, I can’t paste the formula into this editor, it only pastes the result.

            The trick was in my earlier post:

            Could you please post your formula, paste the copied text (rather than a screenshot), select the text, then click CODE on the Text editor tab

            The Code button is second on the right, at the top of the text editor box, and you need to select your pasted text first. Note it needs to be in the Text version of the editor box, not the Visual version.

            That should work. See this for other information.

            • #2172248 Reply
              truesupport
              AskWoody Lounger

              Thanks for the help – but none of these suggestions allows me to paste the formula, only the result.  I must be doing something very basic incorrectly.

              I’ve just typed it out and I hope that is good enough:

              {QUOTE
              {SET Delay 1}
              {SET a{=INT((1-{MERGEFIELD event_start_date \@ M})/12)}}
              {SET b{={MERGEFIELD event_start_date \@ yyyy}+4800-a}}
              {SET c{={MERGEFIELD event_start_date \@ M}+12*a-3}}
              {SET d{MERGEFIELD event_start_date \@ d}}
              {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
              {SET e{=INT((4*(jd+32044)+3)/146097)}}
              {SET g{=INT((4*f+3)/1461)}}
              {SET h{=f-INT(1461*g/4)}}
              {SET i{=INT((5*h+2)/153)}}
              {SET dd{=h-INT((153*i+2)/5)+1}}
              {SET mm{=i+3-12*INT(i/10)}}
              {SET yy{=100*e+g-4800+INT(i/10)}}
              “{dd}-{mm}-{yy}”\@ “d MMMM yyyy”}

      • #2171897 Reply
        Paul T
        AskWoody MVP

        That DOCX is impossible to decipher in LibreOffice.
        Paste the formula into Notepad, save and upload the text file. Or stick it on Pastebin and link via the link button.

        cheers, Paul

        • #2172249 Reply
          truesupport
          AskWoody Lounger

          My apologies, Paul, I didn’t realise a doc would be an issue.

          I’ve just typed the formula into my reply to Kirsty.

      • #2172419 Reply
        Paul T
        AskWoody MVP

        Can you post a link to “date calc tutorial document”?

        cheers, Paul

      • #2174759 Reply
        Paul T
        AskWoody MVP

        You seem to have missed a “SET f” statement.
        You are using “f” in this statement “{SET h{=f-INT(1461*g/4)}}”, but it’s not defined.

        cheers, Paul

        • #2175201 Reply
          anonymous
          Guest

          Hi Paul,

          I mistakenly left that line out of the post, but it is in the formula in my document –

          {SET f{=jd+32044-INT(146097*e/4)}}

          I’ve worked out that by making the changes indicated below in red, I’ve resolved the month/day flip -It seems that formatting the numbers to two places resolves confusion over whether a number is a month or day as dates with days higher than 12 (so no possible confusion with a month) were fine –  however, the February dates still don’t add a day.  I thought it may have something to do with leap year not being accounted for in the formula, but the date is early February.  Still working on it…

          I appreciate any insight you may have.

          {QUOTE
          {SET Delay 1}
          {SET a{=INT((1-{MERGEFIELD event_start_date \@ MM})/12)}}
          {SET b{={MERGEFIELD event_start_date \@ yyyy}+4800-a}}
          {SET c{={MERGEFIELD event_start_date \@ MM}+12*a-3}}
          {SET d{MERGEFIELD event_start_date \@ dd}}
          {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
          {SET e{=INT((4*(jd+32044)+3)/146097)}}

          {SET f{=jd+32044-INT(146097*e/4)}}
          {SET g{=INT((4*f+3)/1461)}}
          {SET h{=f-INT(1461*g/4)}}
          {SET i{=INT((5*h+2)/153)}}
          {SET dd{=h-INT((153*i+2)/5)+1}}
          {SET mm{=i+3-12*INT(i/10)}}
          {SET yy{=100*e+g-4800+INT(i/10)}}
          “{mm}-{dd}-{yy}”\@ “dd MMMM yyyy”}

    Viewing 4 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Calculated date merge field issue

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.