7 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Convert Character to Date format

    gico1972 _

      Any ideas on how i convert a character to a date format.

       

      Problem is that report shows date as DDMMM format with no year and so initially i can only have this as a character format.

       

      Note that all dates will be in advance of todays date and never historical. Therefore 01JAN should show as 01JAN09 and 10AUG will show as 10AUG09.

       

      Belive that i need a calculated field to convert this however i also want to ensure that dates will export into Excel showing correct year.

       

      Any Help on this is most welcome.:)

        • Convert Character to Date format
          Nigel Winton

          Hi again

          Yes you will need a calculated character field to add the year to the day and month you already have then you should be able to use the CTOD() function in Monarch to convert the new field to a date field. Remember to use date/time format for the field type.

           

          Regards

           

          Nigel

            • Convert Character to Date format
              gico1972 _

              Nigel

               

              Never used the CTOD() function before however would the calculated field automatically asume that the year will be the advance dates. Eg 10NOV will show as 10NOV08 and subsequently 10JAN will reflect as 10JAN09?

               

              Thanks for your help.

               

              Regards

                • Convert Character to Date format
                  Nick Osdale-Popa

                  I would append the current year to the field, convert to date, then compare the month to the current month. If it's less than the current month, add 1 year to it.

                    • Convert Character to Date format
                      gico1972 _

                      Thanks. Unfortunetly i am novice still and whilst i understand the logic it will take me some time to work out how to set up these calcualated field.

                       

                      Thanks anyway.

                       

                      Regards

                        • Convert Character to Date format
                          Grant Perkins

                          gico,

                           

                          You seem to be one of many who need to start their Monarch learning process in the deeper ends of the pool rather than build up to it gently. It's funny how often we see that on the forum.

                           

                          Now, your requirement has a number of possibly approaches all of which may look a little daunting but in fact, if broken down into bite sized chunks, are not too difficult and are very useful as a discovery exercise for some of the powerful Monarch features. (That said they can look a bit like a power-press to crack a walnut in this particular context!)

                           

                          I have a model that will do what you want to do and that includes a calculated field for each stage of the process I chose to adopt. I V9 I can include internal Comments to describe what is happening but you won't be able to see them in V8. If you think you would feel comfortable working through the model to identify how I approached your requirement I am happy to make it available but if you don't feel confident about that we would be better looking for a more formal approach since I don't wish to risk introducing unnecessary confusion to your learning curve! Nor do I have time right now to write it up as a full set of instructions that need to assume zero knowledge 'just in case'.

                           

                          Monarch is not difficult to self learn to very useful degree of skill. However avoiding taking on anything that might be confusing by the way it is presented related to current knowledge is important in the early days. Hence my caution.

                           

                          The concepts I used are not difficult individually but could be a bit much to take in en masse if you are still feeling your way with Monarch. On the other hand if you are getting into  Calculated Fields and the functions they use for text manipulation (the 'SPLIT' functions and STR in particular) and have also discovered Lookup Tables and basic concatenation methods then you should have no problem at all working out what is going on from the field formulas without much explanation.

                           

                          Let me know what your thoughts are and we can take it from there.

                           

                          Grant

                            • Convert Character to Date format
                              gico1972 _

                              Grant

                               

                              Thanks very much for your detailed response. I am understning more and more on how fields are broken down which obviously helps.

                               

                              If you have a model already made then i would like to look at the workings as i found that helps a great deal in understanding exactly what strings do what to create calculated fields.

                               

                              Appreciate your help on this. How could i gain access to your ready made model?

                               

                              Thanks

                                • Convert Character to Date format
                                  Grant Perkins

                                  How could i gain access to your ready made model?

                                   

                                  /quote

                                   

                                  Good question.

                                   

                                  I note that Attachments for xmods seem to be possible so lets try that.

                                   

                                  You will need some input data. The Model currently just looks for a 5 character field in the left column in the 10JAN format. I created a sample file cutting and pasting from this and your other post into a txt file. Obviously you would need to adapt this to your full model anyway.

                                   

                                   

                                  If this attachment option fails to work send me a PM with an email address that will accept small attachments and I will forward a zip file with the model and the sample data file.

                                   

                                   

                                  HTH.

                                   

                                   

                                  Grant