Counting overtime in seperate fields?

Hey guys could reeeeeaally use some help.
I'm trying to make my life easier and sending in my hours to the office every month takes me forever.

They want things split in several different ways by adress and times etc.

I have a few columns

Date, Dayoftheweek, Starttime, Lunch, Endtime, address, Regular hrs, Overtime1, Overtime2

Regular hours are 7:00-16:00
Overtime1 is everything before 7:00 same day (Early Start) and 16:00-18:00
Overtime2 is everything from 18:00 until midnight.

So i have figured out how to calculate endtime minus starttime - lunch and that works great.

However now trying to get it to split it into different types of overtime is really confusing..


E.g.
Date.....Day.....Starttime.....Lunch.....Endtime.....Address.....Rglr Hrs.....Over1.....Over2
19/4......Fri........06:30...........1.........16:45.......Newway..........8...........0.5.........0.75


How do i get the regular hrs, over1 and over2 to auto calc themselves?
Any help appreciated!

So you just want the overtime? in hours? or like 1.25? or like 1hr 45mins?

Hi,

For Overtime 1, you can use:

=IF(OR(AND(B2-TIMEVALUE("16:00")>TIMEVALUE("0:00"),B2-TIMEVALUE("16:00")<=TIMEVALUE("2:00")),B2-TIMEVALUE("16:00")>TIMEVALUE("2:00")),MIN("2:00",B2-TIMEVALUE("16:00")),0)+IF(C2-A2>TIMEVALUE("0:00"), C2-A2, 0)

For Overtime 2, you can use:

=IF(B2-TIMEVALUE("18:00")>TIMEVALUE("0:00"),B2-TIMEVALUE("18:00"),0)

It worked on my end. Please let me know if that works for you!

Okay sorry read that wrong my bad, so 0:700 - 16:00 + overtime 1 + overtime 2? should lunch be included? what formula are you using for endtime - start time?

Originally Posted by Kevin UK

Hi jxales

Perhaps something along these lines or you can adapt to suit your needs!

Wow this was 95% what i needed, however is it possible to have the lunch as just a number? 1 or 1.5 etc..
when i try and change it i get the day serial 0.46xxxx

Originally Posted by Excel-course.com

Hi,

For Overtime 1, you can use:

=IF(OR(AND(B2-TIMEVALUE("16:00")>TIMEVALUE("0:00"),B2-TIMEVALUE("16:00")<=TIMEVALUE("2:00")),B2-TIMEVALUE("16:00")>TIMEVALUE("2:00")),MIN("2:00",B2-TIMEVALUE("16:00")),0)+IF(C2-A2>TIMEVALUE("0:00"), C2-A2, 0)

For Overtime 2, you can use:

=IF(B2-TIMEVALUE("18:00")>TIMEVALUE("0:00"),B2-TIMEVALUE("18:00"),0)

It worked on my end. Please let me know if that works for you!

Didn't get it to work, i'll up the workbook so you can see what i tried.
i'm quite novice. I like tinkering with other peoples formula to figure out how they work but beyond that i can't really problem solve or originate :S

Ok so here is the quick and thin of what i'm trying to do.

Fiddled a little with excel-c's formulas so naturally they don't work

sorry for the triple post too :/
won't happen again

Originally Posted by jxales

Ok so here is the quick and thin of what i'm trying to do.

Fiddled a little with excel-c's formulas so naturally they don't work

sorry for the triple post too :/
won't happen again

Thanks for uploading your file. Yes, it was just a different mapping of the fields. Try this:

For Overtime 1:

=(IF(OR(AND(E3-TIMEVALUE("16:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("16:00")<=TIMEVALUE("2:00")),E3-TIMEVALUE("16:00")>TIMEVALUE("2:00")),MIN("2:00",E3-TIMEVALUE("16:00")),0)+IF(TIMEVALUE("7:00")-C3>TIMEVALUE("0:00"), TIMEVALUE("7:00")-C3, 0) - INT(IF(OR(AND(E3-TIMEVALUE("16:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("16:00")<=TIMEVALUE("2:00")),E3-TIMEVALUE("16:00")>TIMEVALUE("2:00")),MIN("2:00",E3-TIMEVALUE("16:00")),0)+IF(TIMEVALUE("7:00")-C3>TIMEVALUE("0:00"), TIMEVALUE("7:00")-C3, 0)))*24

For Overtime 2:

=(IF(E3-TIMEVALUE("18:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("18:00"),0) - INT(IF(E3-TIMEVALUE("18:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("18:00"),0)))*24

It worked on my end using your file, so you shouldn't need to change the field names. I also added extra code, so the output is in number format.

Please let me know if that works for you!

Originally Posted by Excel-course.com

Thanks for uploading your file. Yes, it was just a different mapping of the fields. Try this:

For Overtime 1:

=(IF(OR(AND(E3-TIMEVALUE("16:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("16:00")<=TIMEVALUE("2:00")),E3-TIMEVALUE("16:00")>TIMEVALUE("2:00")),MIN("2:00",E3-TIMEVALUE("16:00")),0)+IF(TIMEVALUE("7:00")-C3>TIMEVALUE("0:00"), TIMEVALUE("7:00")-C3, 0) - INT(IF(OR(AND(E3-TIMEVALUE("16:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("16:00")<=TIMEVALUE("2:00")),E3-TIMEVALUE("16:00")>TIMEVALUE("2:00")),MIN("2:00",E3-TIMEVALUE("16:00")),0)+IF(TIMEVALUE("7:00")-C3>TIMEVALUE("0:00"), TIMEVALUE("7:00")-C3, 0)))*24

For Overtime 2:

=(IF(E3-TIMEVALUE("18:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("18:00"),0) - INT(IF(E3-TIMEVALUE("18:00")>TIMEVALUE("0:00"),E3-TIMEVALUE("18:00"),0)))*24

It worked on my end using your file, so you shouldn't need to change the field names. I also added extra code, so the output is in number format.

Please let me know if that works for you!

This is intense coding really nice job!
It worked flawlessly.

I was hoping that getting the overtime1 and overtime2 i would be able to figure out how to calculate the regular hours between 7-16
but i'm getting a headache trying to break it down.

If it's not too much to ask could you figure that one out for me too or explain how you did it so i can not have a migrain for the next hour :D

Originally Posted by jxales

This is intense coding really nice job!
It worked flawlessly.

I was hoping that getting the overtime1 and overtime2 i would be able to figure out how to calculate the regular hours between 7-16
but i'm getting a headache trying to break it down.

If it's not too much to ask could you figure that one out for me too or explain how you did it so i can not have a migrain for the next hour :D

Great, glad it worked! Thanks for the feedback.

You can get the regular hours by subtracting the overtime hours from the total hours. Try this:
=(((E3-C3)-INT(E3-C3))*24)-H3-I3

Let me know if that works!

KevinUK,

According to Post #1 and #12, regular hours start at 7.

if using your forumla, is it possible to make it so that if it's on a saturday or sunday that it puts everything in overtime2?

Originally Posted by Kevin UK

Hi

In G3 for regular hours & copy down:
=(C3>E3)*("16:00"-"06:00")+(MEDIAN(E3,"16:00","06:00")-MEDIAN(C3,"16:00","06:00"))*24
In H3 for Overtime1 & copy down:
=((E3-C3)*24)-G3-I3
In I3 for Overtime2 & copy down;
=(C3>E3)*("24:00"-"18:00")+(MEDIAN(E3,"24:00","18:00")-MEDIAN(C3,"24:00","18:00"))*24

@ Excel-course.com
My understanding is that regular hrs are 06:00-16:00, the formula in post #10

Returns 1hr in H3,should that not be 0.5hr, H6 should be 2hrs etc, also when you copy down they are returning values for blank cells.

Your formula works great and im trying to make it so that if its saturday or sunday all of the time goes to overtime2

this is what ive tried with google and cant formulate it right excel keeps saying its wrong

=IF(WEEKDAY(B8)=7,((C8>E8)*("16:00"-"07:00")+(MEDIAN(E8;"16:00";"07:00")-MEDIAN(C8;"16:00";"07:00"))*24)-D8,0)

thx for everything