Jump to content
  • 0

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


cordova
 Share

Question

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?

 

 

 

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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:

 

1127931736_totalshift.thumb.png.be6a243dceab90ca4b2dacbc4ca5953d.png

 

 

Link to comment
Share on other sites

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.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...