4 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Extract Time from a Date

    TH _

      Somehow Monarch threw this date in with what I thought was only a time parameter 12/30/1899 07:58:00.  The report has only a time 07:58.  What do I need to do remove the date from this date/time combination to have only 07:58:00 or 00/00/0000 07:58:00?

      Thanks for your help

      TH

        • Extract Time from a Date
          Grant Perkins

          Hi TH,

           

          At a guess I would imagine you have defined the field as a Date/Time field with one of the Date/Time formats rather than just Time.

           

          The date part is the default date (12/30/1899 )displayed for a blank or zero value in the relevant area of the field.

           

          Have you tried changing the format to see what happens?

           

           

          Grant

          • Extract Time from a Date
            TH _

            Grant,

            Yes, I originally had it designated in the time format and a correct duration was being created within the same report.  Even though the date wasn't seen in the time format the dates canceled each other out when the duration tabulation was created.  However, when I tried to create a duration with a linked time formatted figure the duration (subtraction) was way off.  That's when I found the date of 12/30/1899 causing the problem.  An example of what I'd like to see is a duration calculation 08:00:00 to 16:00:00 not 12/30/1899 08:00:00 to 10/04/2006 16:00:00.

            Feel free to ask any questions,

            Love my V8,

            TH

            • Extract Time from a Date
              TH _

              Grant, Played around with it and figured it out.

              This formula allowed only the time to be calculated without the old date:

              Age(time(),time(),7).  Putting "time" in the formula was the answer. 

              Thanks,

              TH

              • Extract Time from a Date
                Grant Perkins

                Dates and times are interesting challenges!

                 

                Time only calculations as per the formula you derived are fine so long as you can be sure the times in use relate to the same day (ie date).

                 

                If not you have to expect to use a date as well as a time. In this case it sounds like the report does not always provide the date, on which basis it would seem safe to assume that the times relate to the same day. Therefore a time only calculation should be a safe option.

                 

                The alternative would be to derive a date from somewhere and concatenate that date with the 'dateless' time value before performing the duration calculation.

                 

                Sounds like you had some good practice with this one.    smile.gif[/img] 

                 

                 

                Grant