• 0

# Time stamp duration between records in hrs

## Question

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.

## Recommended Posts

• 0

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?

Regards,

kristina

##### Share on other sites

• 0

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.

##### Share on other sites

• 0

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

##### Share on other sites

• 0

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)

##### Share on other sites

• 0

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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.