calculate OT after certain hours worked

Hi Guys,

Just wanted to request assistance for this excel file. Regular Hours is 10.5 Hours, i wanted to see the OT and late.

OT - the computation will start after 10.5 hours is completed. But the rule is the valid OT computation should start after 11.5. valid for OT is above 1 hour after you completed the 10.5 hours.

LATE- if 10.5 hours is not completed it will show on this column.

Please refer to the attached file.

Thanks,

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(I have changed it for you - this time )

I am a bit confused. You cay to calc OT after 10.5 hrs, but then you say to calc it after 11.5 hours? Which 1 is it?

Can you show some sample answers in your file please?

I dont see how you get 1:39 hrs OT for the 1st row?
Start 9:00 AM
End 20:39 PM
= 11:39 hrs worked
You said OT starts after 10.5 hrs, so that should only be 1:09 hrs, not 1:39 hrs?

	G	H
2	1:09
3		2:00
4	2:00
5	2:00
6	2:00
7	0:30

G2=IF(D2-C2-(10.5/24)<0,"",D2-C2-(10.5/24))
H2=IF(D2-C2-(10.5/24)<0,(10.5/24)-(D2-C2),"")
both copied down
use Custom Format [h]:mm;@

I have modified my formulas...

	A	B	C	D	E	F	G	H
1	Employee	Schedule	Log in	Log Out	OT	LATE
2	Name1	Flexitime	9:00 AM	8:39 PM	1.39		1:09
3	Name2	Flexitime	10:00 AM	6:30 PM		2		2:00
4	Name3	Flexitime	10:00 AM	10:30 PM			2:00
5	Name4	Flexitime	11:00 AM	11:30 PM			2:00
6	Name5	Flexitime	2:00 PM	2:30 AM			2:00
7	Name6	Flexitime	4:30 PM	3:30 AM			0:30
8			10:00 AM	20:39 PM
9			10:01 AM	9:31 PM			1:00

G2=IFERROR(IF(D2-C2-(10.5/24)<0,"",D2-C2-(10.5/24)),"")
H2=IFERROR(IF(D2-C2-(10.5/24)<0,(10.5/24)-(D2-C2),""),"")
both copied down as needed

In simple you have add one column "B" : Schedule In & column "C" : Schedule Out
"D" : Log in
"E" : Log Out
"F" : OT (Formula : =E2-C2) format as "Time" 13:30

Originally Posted by avk

In simple you have add one column "B" : Schedule In & column "C" : Schedule Out
"D" : Log in
"E" : Log Out
"F" : OT (Formula : =E2-C2) format as "Time" 13:30

I dont follow what you are doing here, avk, can you post a quick sample please?

Here is sample file.

Originally Posted by avk

Here is sample file.

Not really sure where you got the start and end times from, I did not see that in the OP's posts (could have missed it though lol)

Originally Posted by concepcion.jensen

Sir how about the result that will disregard the OT if its below 1 hour, but will compute if the OT is above 1 hour.

see if this will do what you want...
=IFERROR(IF(ROUNDDOWN(D2-C2-(10.5/24),5) < ROUNDDOWN(1/24,5),"",D2-C2-(10.5/24)),"")

OK make up your mind.
I gave you OT for working more than 10.5 hours, like you asked.
Then you said it must be an hour over 10.5, and I gave you that
Now you say (again) you want it more than 10.5.

I strongly suggest that you attach a file showing all possible variations of what you want (and dont want), so that we can all stop guessing

Just to make sure I have the rules right...
You earn OT after working more than 10.5 hours.
But you have to work at least 1 hour OT before you will be considered for payment
If you work less than 1 hour OT, you do not qualify to be paid (you worked that time for free)
If you work more than 1 hour OT, all time after 10.5 counts as OT

Is that correct?

Oh and by the way, all those "times" in your file, are not really times, they are text that just looks a bit like time
0531h is text
05:31 AM is real time

1. So you want OT less than an hour to NOT be shown...or to BE shown?

2. did you see my comment regarding what you have entered as "time"?

Is your time really entered as in your latest sample file?

Do my suggested formulas not work for you?

If not, what do they give, compared to what you want?

Sorry, I am not going to correct your times. Fix those and re-upload, so that I have realistic data to work with

Hi FDibbins, Please refer to the attached file.

Sorry for the delay in responding.

Did you try my suggested formulas in that file?

yes, I saw that file. As I asked in post 31 - did you try my suggested formulas yet?

OK in F5, how did you get 0.58?
9:35 Am to 7:50 PM is 10:15. You said to show Late that is less than 10.5 hours, si that should be 0:15, which my "late" formula gives you...
F5=IFERROR(IF(E5-D5-(10.5/24)<0,(10.5/24)-(E5-D5),""),"")
copied down

You dont show anything in the OT column, but in row 4, you have 5:31 AM to 6:52 PM which is 13:21, so after 10.5 hours, that gives 2:51 OT...which my "OT" formula calcs...
G4=IFERROR(IF(ROUNDDOWN(E4-D4-(10.5/24),5) < ROUNDDOWN(1/24,5),"",E4-D4-(10.5/24)),"")
copied down

If you format them to Custom [h]:mm they should show up what you need

Sure. Below your table, I show what the actual hour difference is, as well as the hour difference minus 10.5 hours. because excel does not like negative time (it gives an error) I used ABS() to just show the difference - ignore the formula, look atthe time

Happy to help and thanks for the feedback