7 Replies Latest reply: May 15, 2014 9:52 AM by Tom Strachan RSS

    Time calc

    Tom Strachan

      Hello everyone.  Dumb question.  I have a file that expresses a date as an integer, the number of days since 1/1/1900.  For example 38123.

       

      I have to convert this number to a more normal date format such as xx/xx/xx.

       

      Easy in Excel, but I'm stumped.  what can I do in Monarch to handle this conversion?

       

      Thanks!

        • Time calc
          Tom Strachan

          Hello everyone.  Dumb question.  I have a file that expresses a date as an integer, the number of days since 1/1/1900.  For example 38123.

           

          I have to convert this number to a more normal date format such as xx/xx/xx.

           

          Easy in Excel, but I'm stumped.  what can I do in Monarch to handle this conversion?

           

          Thanks!

          • Time calc
            Data Kruncher

            Hi Tom,

             

            I don't think this is a dumb question at all; quite to the contrary.

             

            So, thinking that you can  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001244#000000"]subtract one date from another[/url] to get the number of days between those dates, I thought you should be able to add a number of days to a date to determine another date.

             

            Given that Excel returns 38912 for 14-Jul-2006, if you define a calculated date field with the following formula:

            [font="courier"]CtoD("01-01-1900","m/d/y")+-2  /font[/quote]Monarch will return a date of July 14, 2006 when the Number of Days numeric field equals 38912. Not sure of the specifics as to why you need the -2, but you do.

             

            That work for you?

             

            Kruncher

             

            Edit: In fact, there could be any number of applications for adding a number to a date found within a report: calculating payment due dates (maybe with an external lookup to a customer file to determine the customer's payment terms in days + the invoice date, if not everyone has, say, 30 day terms), expiration/shelf dates of food items... Does anyone currently use a calculation like this?

             

            [size="1"][ July 14, 2006, 10:40 PM: Message edited by: Data Kruncher ][/size]

            • Time calc
              Data Kruncher

              Hi Tom,

               

              I don't think this is a dumb question at all; quite to the contrary.

               

              So, thinking that you can  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001244#000000"]subtract one date from another[/url] to get the number of days between those dates, I thought you should be able to add a number of days to a date to determine another date.

               

              Given that Excel returns 38912 for 14-Jul-2006, if you define a calculated date field with the following formula:

              [font="courier"]CtoD("01-01-1900","m/d/y")+-2  /font[/quote]Monarch will return a date of July 14, 2006 when the Number of Days numeric field equals 38912. Not sure of the specifics as to why you need the -2, but you do.

               

              That work for you?

               

              Kruncher

               

              Edit: In fact, there could be any number of applications for adding a number to a date found within a report: calculating payment due dates (maybe with an external lookup to a customer file to determine the customer's payment terms in days + the invoice date, if not everyone has, say, 30 day terms), expiration/shelf dates of food items... Does anyone currently use a calculation like this?

               

              [size="1"][ July 14, 2006, 10:40 PM: Message edited by: Data Kruncher ][/size]

              • Time calc
                Grant Perkins

                Hi Tom,

                 

                If you know the base date (in this case 01-01-1900) you can set up a calculated field directly to store the constant (maybe as a run time parameter?) and express the formula as +(days-2).

                 

                I think the issue about the -2 relates to the 01-01-1900 date being day 1 (So day zero is 31-12-1899 which you could use instead as the runtime parameter.

                 

                I suspect the final adjustment (the minus 1) is a similar issue in that the day number, when converting to a date by calculation, is assumed to be a completed number of days thus the date offered is the next day date not the 'current day' date. In other words the calculation is geared up for the results of subtraction rather than addition.

                 

                Personally I think I would settle for a calculated date format field with

                +(DAYS-1)

                 

                as a fairly memorable way write the formula.

                 

                HTH.

                 

                Grant

                 

                Edit: - In the above formula the date represents the field that would be used for the PARAMETER FIELD.

                 

                To make the formula using a CONSTANT DATE would require;

                 

                {31-12-1899}+(DAYS-1)

                 

                [size="1"][ July 17, 2006, 04:19 AM: Message edited by: Grant Perkins ][/size]

                • Time calc
                  Grant Perkins

                  Hi Tom,

                   

                  If you know the base date (in this case 01-01-1900) you can set up a calculated field directly to store the constant (maybe as a run time parameter?) and express the formula as +(days-2).

                   

                  I think the issue about the -2 relates to the 01-01-1900 date being day 1 (So day zero is 31-12-1899 which you could use instead as the runtime parameter.

                   

                  I suspect the final adjustment (the minus 1) is a similar issue in that the day number, when converting to a date by calculation, is assumed to be a completed number of days thus the date offered is the next day date not the 'current day' date. In other words the calculation is geared up for the results of subtraction rather than addition.

                   

                  Personally I think I would settle for a calculated date format field with

                  +(DAYS-1)

                   

                  as a fairly memorable way write the formula.

                   

                  HTH.

                   

                  Grant

                   

                  Edit: - In the above formula the date represents the field that would be used for the PARAMETER FIELD.

                   

                  To make the formula using a CONSTANT DATE would require;

                   

                  {31-12-1899}+(DAYS-1)

                   

                  [size="1"][ July 17, 2006, 04:19 AM: Message edited by: Grant Perkins ][/size]

                  • Time calc
                    Tom Strachan

                    Thanks very much both of you, I'll use these ideas immediately.  It does simplify the formula considerably, and reduces further coversion work (i.e. eliminates the need for Excel to do the work).

                    • Time calc
                      Tom Strachan

                      Thanks very much both of you, I'll use these ideas immediately.  It does simplify the formula considerably, and reduces further coversion work (i.e. eliminates the need for Excel to do the work).