Time stamp duration between records in hrs

[Sherif]

Adding time stamp to each record 

Adding new record to ID 5 for example I Need calculated field in my report or  form  to calculate the time in hours between current  record and the previous record having the same ID.

 

 

[cheonsa]

Hi @Sherif,

Could you please elaborate your desired workflow? Initially, as I understand, you need to calculate the time in hours between the current and previous record with the same ID. Would you like to save it on your Table or just display it in a reports? 

You may refer to this article for reference in your Formula: https://howto.caspio.com/function-reference/

 

Regards,

kristina

[Kurumi]

Hi @Sherif,

You could check this site for getting difference between hours: https://www.w3schools.com/sql/func_sqlserver_datediff.asp

[Hastur]

Hello @Sherif

The formula should look something like this:

DATEDIFF(
  hh,
    (SELECT TOP 1 (Name_Of_Your_Date_Field)
    FROM Your_Table_Name
    WHERE Name_Of_ID_Field = target.[@field:Name_Of_ID_Field] 
    AND Name_Of_Your_Date_Field < target.[@field:Name_Of_Your_Date_Field]
    ORDER BY Name_Of_Your_Date_Field desc),
  target.[@field:Name_Of_Your_Date_Field]
)

This formula selects the last one entry with the same ID you have entered and return the difference in hours between submitted date and selected date.

[Sherif]

@Andrew

Thanks working perfectly but as integer only ,

Any way to get decimals ,like 2.5 hrs for example  

No rounds .

I want treat 90 min is 1.5 hours 

Maybe get minutes and divide by 60 

[Hastur]

Hello @Sherif

To implement such a formula you may use modulo division.

The formula should look something like this:
CAST( ((FULL_AMOUNT_OF_MINUTES  -  FULL_AMOUNT_OF_MINUTES % 60) / 60) AS NVARCHAR)  +  '.'  +  CAST( ((FULL_AMOUNT_OF_MINUTES % 60) * 100 / 60) AS NVARCHAR)

[Kurumi]

Hi! Caspio now offers integration with OpenAI, so you can use extensions to leverage AI to update your data based on a prompt. Here's a sample use case to get the total worked hours based on Time In and Time Out fields.

Request:
Calculate the total working hours for the following time entries:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

The format should be HH:MM

Insert 'File a leave' if there are no values in:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

Return only the final result.

Result:

Change the 'Clock_In' and 'Clock_out' fields to your right field names.

To learn more about Extensions, you can check it here: https://howto.caspio.com/integration/extensions/