Formula calculation

[Minnal_guy2002]

Dear Experts,

Please guide me on how to calculate the working hours.
E.G : If the person clock in at 7.30AM and go back home at 8.32PM.
the actual working time is 8.00AM till 8.00PM with 2hours break with differntent time.
the same time need to calculate their overtime.
Regular hour is 8hrs
Thank you

 

[AliGW]

Total: =IF([@[Clock In]]="","",[@[Clock Out]]-[@[Clock In]]-(2/24))
Regular: =IF([@[Clock In]]="","",MIN(20/24,[@[Clock Out]])-MAX(8/24,[@[Clock In]])-(2/24))
Overtime: =IF([@[Clock In]]="","",[@[Total Hrs]]-[@[Regular Hrs]])

 

[Minnal_guy2002]

Thank
you @AliGW for you time to update the formula, in the column Total Hrs (L2) we need the total hour calculation not in time format but in numeric format.

 

[AliGW]

What do you mean? Just change the column's formatting yourself from TIME to NUMBER or CUSTOM (hh:mm) ... But why would you NOT want it in time format? It's a time value ...

Remember: you failed to fill in any expected results, so I had to guess what you wanted.

 

[Minnal_guy2002]

What do you mean? Just change the column's formatting yourself from TIME to NUMBER or CUSTOM (hh:mm) ... But why would you NOT want it in time format? It's a time value ...

Remember: you failed to fill in any expected results, so I had to guess what you wanted.

Hi @AliGW sorry for the not clear message to you, below are the clear ideas for you.

1) eg: Staff come in at 7.23 to punch in but the actual time to start work is 8am till 8pm
2) minus the 2 hours break time
3)The total Hrs should be shown in Number e.g (if 8.00am - 8.00pm = 10 after minus the break for 2 hours)
4) For the regular hour is normal hours as 7.5hours after that we should calculated as overtime.

Thank you

 

[AliGW]

4) No - it's 10 hours - you said 0800 to 2000 minus 2, which is 12-2=10. You also said 8 hours, which it's not, and it's definitely not 7.5!

You have shifted the goalposts: examine the formulae I gave you and try to sort out the changes you need yourself. This is a HELP forum, not an agency that does all the work for you, and it's FREE, so you need to make an effort here.

 

[AliGW]

Try this in M2:

=IF([@[Clock In]]="","",MIN(8/24,MIN(20/24,[@[Clock Out]])-MAX(8/24,[@[Clock In]])-(2/24)))

It's up to you now to tweak this for any shifted goalposts.