7 Replies Latest reply on Mar 5, 2018 12:09 PM by MarkLarvo

    Merge Date and Time Fields

    posborne Apprentice

      I have a date field and a time field on my form in Incident, and would like to merge the two into 1 date/time field, but can't seem to get an accurate result.

       

      I tried making a Calculation Rule in Incident, but the result is off by about 49 hours.

       

      Heat Version: 2014.3

       

      This is what I have so far:

      For Field: Date/Time

      Expression: $(FormatDate(ToString(Date1),"MM/dd/yyyy") + " " + FormatDate(Time1,"hh:mm tt"))

        • 1. Re: Merge Date and Time Fields
          SusanJS Specialist

          Is your field an actual Date/Time field type or a text field?

          • 2. Re: Merge Date and Time Fields
            posborne Apprentice

            The target field is a date/time field, but I am open to options on a text field as well.

            • 3. Re: Merge Date and Time Fields
              SusanJS Specialist

              Is it always off by 49 hours?

              Are you able to duplicate the issue using an Update Object Quick Action?

              I'm on version 2016.1 and your expression works just fine both as an editing rule and a calculation rule.  It also works as a quick action for both Date/Time fields and text fields.  There may have been some tweaking between 2014.3 and 2016.1 on date and time fields and calculations.  Have you spoken with HEAT Software Support?

              • 4. Re: Merge Date and Time Fields
                MarkLarvo Specialist

                Hello,

                 

                How in the heck are you getting this to work?!?

                 

                I am on 2017.2 and I am not having any luck. :-(

                 

                I entered your expression above a pressed Simplify Expression it is converted to:

                 

                $(DateTime(FormatDate(DateTime(ML_DueDate + " " + ML_DueTime),  "mm/dd/yyyy hh:mm")))

                 

                Now when I use the form and select a date or time the following error pops up.

                 

                2018-03-03 17_15_15-heattest.kirkland.com_Incident_ 14103.png

                 

                The end goal is to be able to use this DueDate with the TargetDate to determine correct escalation dates/times. Also to sort dashboards and lists based on Due/Target priorities.

                 

                Hoping one of you brilliant people can come to my rescue! Thanks - Mark.

                • 5. Re: Merge Date and Time Fields
                  MarkLarvo Specialist

                  The answer to this is to use a Before Save Rule to add the two fields together.

                   

                  Edit Rules and Calculation Rules DO NOT work.

                   

                  Before Save Rule $(DateTime(FormatDate(DateTime(ToString(ML_DueDate)),  "MM/dd/yyyy") + " " +     FormatDate(ML_DueTime,  "hh:mm tt")))

                   

                  Fields Used:

                  ML_DueDate: Type Binding - Date

                  ML_DueTime: Type Binding - Time

                  ML_DueDateTime: Type Binding - Date & Time

                   

                  So thrilled as I have been trying to figure this out for a long time.

                   

                  Thank you to those who have posted!!! Hope to meet you in Texas in May. Mark

                  • 6. Re: Merge Date and Time Fields
                    MarkLarvo Specialist

                    And I too, have found that the date and time were off by a day and some hours. I assume this has to do with data entry vs UTC time and the timze zone set on our servers.

                     

                    Here is what finally worked: (Note the AddDays and the Timezone additional)

                     

                    $(DateTime(FormatDate(DateTime(ToString(AddDays(1,  DueDate))),  "MM/dd/yyyy") +     " " + FormatDate(DueTime,  "hh:mm tt",  "America/Chicago")))

                    • 7. Re: Merge Date and Time Fields
                      MarkLarvo Specialist

                      ACK!

                       

                      I just tested this using a computer set to another timezone and the day and time are incorrect. Back to the drawing board! I'll post when I have it sorted out.

                       

                      That's what I get for thinking I had it solved at home and not doing my due diligence in testing. :-(

                       

                      Mark.