5 Replies Latest reply: May 15, 2014 9:52 AM by abbarition _ RSS

    Concatenate date

    abbarition _

      I'm sure this should be an easy thing to do, but for the life of me I can't figure it out. I have a date that I am getting off my text file. The date will never be the first day of the month, but I would like to create a calculated field to show the first day of the month I can use to match information from another source.

       

      For example, if the report date is 9/4/2004, I would like a calculated field to show 9/1/2004. I tried several variations of formulas such as Month(rptdate) + "/1/" + Year(rptDate).  I've tried using "+" and ".And." between the items. I’ve also tried using the Ctod & JulianToDate functions in combination with the above (or a variation of the above). I receive one of two errors (I can't remember the first error) but the second and most prevalent error is "Operand Types".

       

      Since it seems my error is usually caused by trying to combine the three items (month, 1, & year), is there something I can use besides "+" or ".And." to combine these? Or, is there a function I am overlooking?

       

      I’m using Monarch 7 Pro.

      Thanks!

        • Concatenate date
          abbarition _

          I'm sure this should be an easy thing to do, but for the life of me I can't figure it out. I have a date that I am getting off my text file. The date will never be the first day of the month, but I would like to create a calculated field to show the first day of the month I can use to match information from another source.

           

          For example, if the report date is 9/4/2004, I would like a calculated field to show 9/1/2004. I tried several variations of formulas such as Month(rptdate) + "/1/" + Year(rptDate).  I've tried using "+" and ".And." between the items. I’ve also tried using the Ctod & JulianToDate functions in combination with the above (or a variation of the above). I receive one of two errors (I can't remember the first error) but the second and most prevalent error is "Operand Types".

           

          Since it seems my error is usually caused by trying to combine the three items (month, 1, & year), is there something I can use besides "+" or ".And." to combine these? Or, is there a function I am overlooking?

           

          I’m using Monarch 7 Pro.

          Thanks!

          • Concatenate date
            Grant Perkins

            [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000273#000000"]Here[/url] is something similar from a while back. A couple of solutions offered though I think V7 offers some alternatives which might be simpler.

             

            Edit:

             

            And here it is:

             

            Calculated field defined as a date with this formula.

             

            -Day(-1)

             

             

            Grant

             

             

                Originally posted by abbarition:

            I'm sure this should be an easy thing to do, but for the life of me I can't figure it out. I have a date that I am getting off my text file. The date will never be the first day of the month, but I would like to create a calculated field to show the first day of the month I can use to match information from another source.

             

            For example, if the report date is 9/4/2004, I would like a calculated field to show 9/1/2004. I tried several variations of formulas such as Month(rptdate) + "/1/" + Year(rptDate).  I've tried using "+" and ".And." between the items. I’ve also tried using the Ctod & JulianToDate functions in combination with the above (or a variation of the above). I receive one of two errors (I can't remember the first error) but the second and most prevalent error is "Operand Types".

             

            Since it seems my error is usually caused by trying to combine the three items (month, 1, & year), is there something I can use besides "+" or ".And." to combine these? Or, is there a function I am overlooking?

             

            I’m using Monarch 7 Pro.

            Thanks! [/b][/quote]

             

            [size="1"][ May 18, 2006, 12:50 PM: Message edited by: Todd Niemi ][/size]

            • Concatenate date
              Grant Perkins

              [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000273#000000"]Here[/url] is something similar from a while back. A couple of solutions offered though I think V7 offers some alternatives which might be simpler.

               

              Edit:

               

              And here it is:

               

              Calculated field defined as a date with this formula.

               

              -Day(-1)

               

               

              Grant

               

               

                  Originally posted by abbarition:

              I'm sure this should be an easy thing to do, but for the life of me I can't figure it out. I have a date that I am getting off my text file. The date will never be the first day of the month, but I would like to create a calculated field to show the first day of the month I can use to match information from another source.

               

              For example, if the report date is 9/4/2004, I would like a calculated field to show 9/1/2004. I tried several variations of formulas such as Month(rptdate) + "/1/" + Year(rptDate).  I've tried using "+" and ".And." between the items. I’ve also tried using the Ctod & JulianToDate functions in combination with the above (or a variation of the above). I receive one of two errors (I can't remember the first error) but the second and most prevalent error is "Operand Types".

               

              Since it seems my error is usually caused by trying to combine the three items (month, 1, & year), is there something I can use besides "+" or ".And." to combine these? Or, is there a function I am overlooking?

               

              I’m using Monarch 7 Pro.

              Thanks! [/b][/quote]

               

              [size="1"][ May 18, 2006, 12:50 PM: Message edited by: Todd Niemi ][/size]

              • Concatenate date
                abbarition _

                A virtual plethora of solutions! Thanks again!

                • Concatenate date
                  abbarition _

                  A virtual plethora of solutions! Thanks again!