3 Replies Latest reply on Aug 27, 2013 2:25 PM by philmodjunk

    If Statement issue with Time Calculation

    ShaikhAtta

      Title

      If Statement issue with Time Calculation

      Post

           Hello, 

                           I am applying a formula in which if the time is less than or equal the value set in a field the status will show CHECKIN and if it is more than or equal to another field then it will show CHECKOUT

           Here is the formula 

           If ( Time < StandardTime::TimeCheckIn ; "Check In"; If ( Time > StandardTime::TimeCheckOut; "Check Out" ))

      Value I am using in StandardTime::TimeCheckIn = 09:00:00 AM

      Value I am using in StandardTime::TimeCheckOut = 05:00:00 PM

            

      Is the above correct?

        • 1. Re: If Statement issue with Time Calculation
          philmodjunk

               And what should be the result if time is > TimeCheckIn or time is < timeCheckOut?

               Given that you are using a table occurrence name: StandardTime, this suggests that your data comes from a related table so the relationship you define will affect whether your calculation works. And the three fields must be all time or all timestamp or calculation fields that return that type. (and you won't be able to mix time and timestamp data types, they have to be all one or all the other.)

               And what you have can be simplified with a case function:

               case ( Time < StandardTime::TimeCheckIn ; "Check In";
                         Time > StandardTime::TimeCheckOut; "Check Out" )

          • 2. Re: If Statement issue with Time Calculation
            ShaikhAtta

                  

                 Thanks Phil, 

                  Actually I have a table TIME where this conditional case calculation is being performed, the table has records of different time in this format: 09:45:00 AM, 05:52:12 PM, 04:21:00 PM, 08:54:00 AM

                 I want the case function to mark CHECK IN where the time is less than 09:00:00 AM and CHECK OUT where it is more than 05:00:00 PM

                 So the values in Standard Time::TimeCheckIn and Standard Time::TimeCheckOut are as below.

                  

            Value I am using in StandardTime::TimeCheckIn = 09:00:00 AM

            Value I am using in StandardTime::TimeCheckOut = 05:00:00 PM

                  

            • 3. Re: If Statement issue with Time Calculation
              philmodjunk

                   Your last post does not add any new information. The format used to display the time does not affect the value stored in the field as long as it is of type time and not text.

                   StandardTime::TimeCheckIn and  StandardTime::TimeCheckOut both need to be of type time and they need to have a valid relationship to the table where you have defined the Time field  in order for this to work.

                   And both your original expression and the one I suggested with Case will return an empty result when the value in time is both greater than 9 am and less than 5 pm such as a time of 3 pm. That may or may not be an issue for you.