# Excel Formula to Calculate Time Card with OverTime and DoubleTime

1. ## Excel Formula to Calculate Time Card with OverTime and DoubleTime

Hello,
I am having an issue with excel. I am new with formulas so I am unable to figure this one out. I do not know much about how to create formula's. My spreadsheet TimeCard needs to calculate Regular Hours, OverTime hours and Doubletime hours worked.

The issue I am having is, I have been asked to format the spreadsheet to show if an employee worked 13 hours that day, then the Regular Hours Worked (column L) should only show 8 hours, and the remaining overtime hours worked (column M) should show 4 hours, and the Doubletime hours worked (column N) should show 1 hour to equal the 13 hours. (anything over 4 hours of overtime is considered double time.)

I can only get the spreadsheet to calculate all the hours ----8 hours show in regular hours column L and the remaining 5 hours show in column M. How can I get the spreadsheet to calculate correctly to where any remaining overtime hours over 4 hours in column M automatically roll over to the doubletime column N?

Any help will be much appreciated. I also hope I explained myself correctly. Thank you for your time. I have attached a spreadsheet

2. ## Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

instead of IF I'd use MIN and MAX functions
L3:N3 resp. and copy down:
Formula:
`Please Login or Register  to view this content.`

remember to format column L as general or number

3. Kaper, thank you so much. I will try it and let you know how it works for me.

4. ## Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

Kaper,
Thank you so much. That worked for me. Thank you. I surely do appreciate your help. I do have one other question.

How do I get the formula to calculate if they worked into the following day without it showing -hours in the hours column? so person worked 12 pm until 12:30 am the next day.

5. ## Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

The easy way would be to compare two always filled in columns and if end hr is earlier than start hr then it was work in night and we have to add 1 day to a difference (I hope nobody works 24 hrs+ ):

so if(K3<F3,1;0)
but to make formulas shorter we could write it just
+(K3<F3)
Formula:
`Please Login or Register  to view this content.`

6. ## Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

see Tatum Jenkins and Erik Ramirez in attached file

7. ## Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

KAPER,

IT WORKED THANK YOU SO MUCH. haha Nobody works more than 24 hours in a day. Its just that sometimes the employees work early evening and carryon til the next day.

I really appreciate all of your help. Do you by chance tutor for excel or give any suggestions on where I can get tutoring besides going to school to take classes? I can sure use it.

There are more things that I would like to learn in excel and at this point it wouldn't hurt to get teaching for it.

8. ## Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

Well, for sure not more than 24h/day :-) but I know the case, when someboty is working continously from say Nov 14th 10:00 AM to Nov 15th 2:00 PM (sounds like 28hrs) with 4 breaks (including 9hrs sleep break, and 3 1hr meals breaks) - so in total 16hrs) and then have 1 day off. (sleeping is on-site).

Thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)