Get total time from two time-in fields, two time-out fields, and break time

[cordova]

Hi,

I have an application that allows employees to time in and time out twice (so on my table i have four fields, two for time-in and two for time-out, all Date/Time fields) and have a break time as long as they want (and I store the break time on a Date/Time field wherein if an employee took a break of one hour and 30 mins, the break time field would look something like "12/30/1899 01:30:00" and I don't necessarily care about the date because I only used the hour and minute as the placeholder for the break time spent by the user). My question is how can I get the total minutes spent by the customer for that particular day?

The formula should go something like:

((time difference between first time-in and first time-out) + (time difference between second time-in and second time-out)) - break time

 

So if I have:

Time-in 1: 02/15/2022 08:00:00

Time-out 1: 02/15/2022  10:00:00

(wherein time-in 1 and time-out 1 returns two hours, meaning the employee rendered 2 work hours)

 

Time-in 2: 02/15/2022 10:30:00

Time-out 2:  02/15/2022 15:00:00

(wherein time-in 2 and time-out 2 returns 4 and a half hours, meaning the employee rendered 4..5 work hours)

 

Break time: 02/15/2022 00:45:00

(which means the employee took a 45 min hour break, and have this 45 min break subtracted from the 6.5 hours rendered (2 work hours + 4.5 work hours), which would total to 5/75 hours or 345 minutes)

 

Is there a way to do this on a formula field in the table?

 

 

 

[futurist]

Hi @cordova,

You can use the following formula for this:

 

(Datediff(minute,[@field:Date1], [@field:Date2])) + (Datediff(minute,[@field:Date3],  [@field:Date4])) 

- (((CONVERT(VARCHAR(2),[@field:Date5],108)) * 60) + ISNULL(RIGHT(CONVERT(VARCHAR(5),[@field:Date5],108), 2), 0))

 

Where Date1 is the first time-in, Date2 is the first time-out. Date3 is the second time-in, Date4 is the second time-out, and Date5 is the break time.

 

I have also tried this using the Date/Time values you have provided and I was able to get the 345 minutes:

 

 

 

[cheonsa]

Hello, 

For more details about Formula allowed in Caspio, you may check this link:

https://howto.caspio.com/function-reference/