3 Replies Latest reply on Aug 27, 2013 9:09 AM by philmodjunk

    Getting StartTime and Endtime from TimeStamp Field but DateWise

    ShaikhAtta

      Title

      Getting StartTime and Endtime from TimeStamp Field but DateWise

      Post

           Hello I have a table with fields :

           UserId        TimeStamp

           123              26/08/2013 9:04:08 AM

           124              26/08/2013 9:05:15 AM

           124              26/08/2013 9:05:20 AM

           123              26/08/2013 17:00:15 PM

           124              26/08/2013 18:01:45 PM

            

           I have created 2 fields CheckIn and CheckOut in the same table. What calculation can I use to get the timing from the timestamp field which will then feed into the CheckIn and CheckOut fields as per the timing of the employee to come and leave the office. 

           Example the standard check in timing is 9:00:00 AM and check out timing is 17:00:00 PM

            

           Also as you can see in the above record the Userid 124 has checked in twice (124              26/08/2013 9:05:15 AM and 124              26/08/2013 9:05:20 AM), so it means I will require the first check in time which will be  26/08/2013 9:05:15 AM.

            

        • 1. Re: Getting StartTime and Endtime from TimeStamp Field but DateWise
          philmodjunk

               Why use the same field for checking in and out? It will be easier to have one field for in and one for out.

               Then subtracting the two fields can compute elapsed time in seconds.

               and GetAsTime ( TimestampField ) can be used to extract just the time value.

          • 2. Re: Getting StartTime and Endtime from TimeStamp Field but DateWise
            ShaikhAtta

                 Phil I would have been relaxed if it was like this, but the problem is this that it is an external database structure which stores the checkin and checkout in one field from where I am getting it into Filemaker and then I have to process it so I can get the CheckIN and CheckOUT seperated. 

            • 3. Re: Getting StartTime and Endtime from TimeStamp Field but DateWise
              philmodjunk

                   You'll have problems with that. People forget to check in and check out very frequently. Any system you set up has to check for and handle that possibility.

                   A relationship can be set up to this table that matches by ID and a date to match to just those clock in/out records for that user for that day. Your script can then loop through those records for each user to popuplate your fields and flag cases where there is an odd number of records. Of course if you have individuals that work a night shift where they clock in before midnight and clock out after midnight, this will require a different approach.

                   You could also simply sort your records by User and also by timestamp and use a script that loops through them processing each record logging the user in and logging them out, while flagging cases where the interval between in and out indicate that a user failed to clock in or out.