8 Replies Latest reply: May 15, 2014 9:51 AM by Grant Perkins RSS

    Date format...

    Tim L.

      I have a report that has the date in yyyy  dd  mm format. I would like to calculate "age" by subtracting the date from today's date. Is this possible?

       

      Note: there are two spaces seperating each part of the string and that the day portion can be 1 or 2 chracters.

       

      Thanks for any help

        • Date format...
          Data Kruncher

          Tim,

           

          Welcome to the group!

           

          Trap and define your Year, Month and Day fields as Character type fields.

           

          Now build a calculated numeric field. Let's call it Days Outstanding. The formula for this field could be as follows:

          [font="courier"]Today()-Ctod(Year"/"Month"/"Day,"y/m/d")[/font][/quote]Depending on how you need to interpret date differences, you may want to add a "+1" to that formula so that a date of Today minus the same date would result in "1" and not "0".

           

          That should do it for you. Please so let us know if this solves your challenge.

           

          Kruncher

          • Date format...
            Grant Perkins

            Tim,

             

            Kruncher's solution sounds rather nice.

             

            As an alternative you could look at a solution quite similar to  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001242"]this one[/url] from a couple of days ago.

             

            Using the LSPLIT option (for example) you could restructure the data in a calculated field to give you something to do a direct comparison to. (Or easly extract Year, Month and DAY). The only difference is that you have a real day rather than assuming a default of 01. so you would be working with a 3 part split rather than a 2 part split.

             

            If you find you need (or want) to add a leading zero to the day if it is less than 10 you could check the value of that component as you create it.

             

            For example if you checked it as a numeric value you would be able to identify if it was less than 10 and so add a zero during the concatenation  process. Or you could simply check the LENght of the characters extracted by the split and add a leading zero if the result was 1. (Can the MONTH portion also be one or two characters?)

             

            The tools available for manipulation will very slightly between Monarch versions - you will get a few additional functions with each release -  but the principles are pretty much the same.

             

            I think if you add in the "/" as part of the concatenation it will give the result you want anyway - did for me using version 8 and this formula. If you leave them out it will work but not for your uncorrected single digit days.

             

            Your original date string, selected as a single 10 character field, is here called 'date_field' and gives a yyyy/mm/dd format result.

             

            CtoD(LSPLIT(date_field,3," ",1)"/"LSPLIT(date_field,3," ",3)"/"LSPLIT(date_field,3," ",2))

             

            So

             

            Today()- ctod(LSPLIT(date_field,3," ",1)"/"LSPLIT(date_field,3," ",3)"/"LSPLIT(date_field,3," ",2))

             

            (Effectively 'TODAY() - ')

             

            would likely do the trick.

             

            In effect this is pretty much the same as Kruncher's solution just approached from a slightly different starting point and passing along a parallel path!

             

            HTH.

             

            Grant

             

            Edit - Typo

            • Date format...
              Tim L.

              Well, I'm getting closer (I think)...

               

              so far I can get the date from

              1985  28  4 (y/d/m) - I believe that y/d/m is not a format that Monarch recognizes, also there are two spaces between each portion

               

                to

               

              1985/4/28 (y/m/d)

               

              I have tried

                Today()-Ctod(,"y/m/d")

               

              When I choose "Type" Numeric, I end up with a number 13205, that does not resemble a date.

               

              When I choose "Type" Date/Time, I get an error message "The expression for this calculated field does not match the specified data type."

               

              I'm thinking it has something to do with the format of my field. I'm a little confused on adding the leading zero for single digit month and day...(to make it a 10 digit date field).

               

              Also is it possible to subtract one date from another?

               

              Thanks for the help so far.

              • Date format...
                Grant Perkins

                Tim,

                 

                The resulting number of the subtraction of one date from another should be the number of days difference  - if negative it means a future date has been used.

                 

                As a matter of interest which version of Monarch are you using?

                Your number - 13205 - taken as a number of days back from 'today' (2006/06/23 on my system) would give 1970/04/28. So 15 years seem to have appeared from somewhere.

                 

                So yes you can subtract one date from another. You can also subtract or add a NUMBER (of DAYS being the implication) from a date and present the result as a date field. So

                 

                TODAY()-365

                 

                would give the result 2005/06/23.

                 

                Obviously leap years would give a slightly different result and the actual cosmetic order of the display will depend upon your system date format settings and any overriding settings you may have set within Monarch.

                 

                Dates, and more especially dates and times differences where the underlying data field is stored in units of seconds, can be a bit challenging to work with and understand.

                 

                I'm sure we will get there!

                 

                 

                Grant

                • Date format...
                  Tim L.

                  Originally posted by Grant Perkins:

                  As a matter of interest which version of Monarch are you using?

                  Your number - 13205 - taken as a number of days back from 'today' (2006/06/23 on my system) would give 1970/04/28. So 15 years seem to have appeared from somewhere.

                  /b[/quote]I am using, Monarch version 7

                  You are correct, the date should have been 1970/4/28 not the 1985 date that I typed.

                   

                  What I'm trying to get is an "Age" field as of a certain date. ex 2006/12/31 - DOB = "Age"

                   

                  Also, could you give me an example of the IF statement you are using to add the leading zero for the single day values that I currently have in my file. I keep getting an "Operand Type" error when I try it.

                   

                  Tim

                  • Date format...
                    Grant Perkins

                    Originally posted by Tim L.:

                    What I'm trying to get is an "Age" field as of a certain date. ex 2006/12/31 - DOB = "Age"[/b][/quote]Aha. The result will always be in days as far as I know. I seem to recall there is a known formula for calcualting ages and taking into account leap years and things - if you need to be that accurate. You could always divide the result by 365 (or 365.25 perhaps) if an approximation would suffice.

                     

                    Version 8 includes an AGE function that will work from Years down to seconds acording to the parameter set for the field.

                     

                     

                    Originally posted by Tim L.:

                    Also, could you give me an example of the IF statement you are using to add the leading zero for the single day values that I currently have in my file. I keep getting an "Operand Type" error when I try it.

                     

                    Tim /b[/quote]In the end I didn't add the zero since the format with the "/"s was recognised as a date field using CtoD.

                     

                    My formula is as posted above and the result is displayed in a numeric field, however I used V8 as the base system and that allowed me to use the break point string (two spaces) when looking for the way to break the data string. That was a new enhancement for V8, so in V7 you will need to use LTRIM to achieve a similar result. Probably whatever you did in the re-position of the date string will be enough anyway provided you Input date format (In the options or the formula) matches the systems expectations.

                     

                    If you are totally stuck I will go into more detail - but it's a bit late for that now tonight where I am! Sleep required!

                     

                     

                    Grant

                    • Date format...
                      Tim L.

                      I have it working... it may not be pretty.

                       

                      First, I re-formatted my original string to a numeric field:

                      Trim(LTrim(LSPLIT(,4," ",1)))"/"Trim(LTrim(LSPLIT(,4," ",4)))"/"LTrim(LSPLIT(,4," ",3))

                       

                      Second, changed the numeric field to a date/time field (mostly for looks)

                      Ctod(,"y/m/d")

                       

                      Finally, the formula I used for my "Age" field.

                      ({31/12/2006}-[DOB-3])/365

                       

                      Thanks, for all the help

                      • Date format...
                        Grant Perkins

                        Hi Tim,

                         

                        Glad you got things working - you have covered quite a lot of useful Monarch functionality in the process!

                         

                        I realised this morning that you can avoid the use of the TRIMs to deal with the double spacing when using the LSPLIT function in this case. (With V8 you could InTrim the field first to give yet another approach.)

                         

                        If you simply base the SPLIT on the number of spaces you can adjust the figures in the original formula to suit - provided there are ALWAYS 2 spaces between each section.

                         

                        ctod(LSPLIT(date_field,5," ",1)"/"LSPLIT(date_field,5," ",5)"/"LSPLIT(date_field,5," ",3))

                         

                        would do it.

                         

                        If the gaps between the data elements are not cnsistent then the TRIM functions would almost certainly be required to ensure the results were consistent.

                         

                        HTH.

                         

                        Grant