Excel Timesheet

Gawd · January 2005

Ok, Im trying to create a Work Timesheet in Excel and I have very little experience with it, I have almost everything how i want. I just need to know what Formulas would let me Calculate Regular time, Overtime, and Doubletime seperately.
I have one column for Time In, one for Time Out, One that Calculate the total Hours, One that Calculates the money made for those hours. But I dont know how to have it calculate in one column, money made for first 8 hours, then another column for money made for the first 3 hours after the initial 8 hours.. then calculate money made on any hours over 11 hours.

Any Help?

Thanks,

Gawd

Ricka182 · January 2005

Hmmmm....., this may help. Tons of formulas.....

Gawd · January 2005

Yes. That is a huge list to go through tho. I would do a search, but im not sure which Formula im looking for.
IF ? MIN ? MAX ?
lol.. Ive been trying all sorts of variations of the IF statement, but nothing is working correctly.

Thanks,

Gawd

Ricka182 · January 2005

This may help explain a bit better. Formulas, and how to use them. Many articles.

TheShadow · January 2005

nested if is needed

first 8 hours
=if(hours < 8, hours * RateOne, 8 * rateone)

next 3 hours
=if(hours <= 8, 0, if(hours > 11, (hours - 8 ) * RateTwo, 3 * RateTwo))

over 11

=if (hours <= 11, 0, (hours - 11) * RateThree)

I tried to write it for clarity if there is some room for minterm groups I leave it as an exercise for you

I am assuming that rateone is an hourly rate and ratetwo is time and a half and rate three is double time of some similar nonsense. Hours would be your hours calculation. If your are using clock time don't forget 24 hour roll overs. Some one may want you to make it work for night shift. If you need that calculation just yell.

edited for stupid smiley on minus 8

Gawd · January 2005

ok, I may have Misinterpreted the formulas you have written there. but this is what i did:

=IF(D10 < 8, D10 * 24 * 10.67, 8 * 10.67 * 24) -First 8 Hours

D10 shows 12:00 Hours, and has a formula of:

=C10+(C10 < B10)-B10 - Which calculates the amount of time between 1:30pm and 1:30am.

after I enter in the first 8 hours formula, its returning the Logical Test as True, and Calculating the full 12 hours. When it should be returning False and doing the second calculation. Am I Right?
Sorry, Im not very experienced with Excel.

Gawd
(BTW, This is more for my own personal use)

Gawd · January 2005

btw..
The Cell Formatting of D10 is [H]:mm and the Cell Formatting of E10(Where the Regular Time Formula Is), is Currency.

Thanks,

Gawd

Gawd · January 2005

.BUMP

This is bugging the hell out of me..

Gawd

TheShadow · January 2005

OK let me try to clear up my formulas. I pulled this out of something I did several years ago so cell numbers are what they are. First here is one that I use for total time which is 24 hour safe. Time may be entered as either 24 hour clock or hours:minutes a space char and AM or PM i.e. 17:35 or 5:35 PM. with start and end times here is the formula with cell C7 a start time D7 a end time. cell F3 as an absolute value with a break time/lunch time amount. Where I am you need 30 minutes break over 6 hours clock time but the option is there to kill it for exempt workers. you can plug it in and go to tools > formula auditing > evaluate then step through it to see how it works. Excel times are really less than one decimal numbers, six hours evaluates as exactly 0.25 and 24 is one.

=IF((D7-C7)<0, 0, IF(AND($F$3>0,(D7-C7)>0.25),D7-C7-$F$3,D7-C7))

Processing the above if I enter 0:30 in F3 (break minutes) 10:15 AM in C7; 10:47 PM in D7 the formula gives 12:02. If F3 is 0:00 then it gives 12:32 or twelve hours and 32 minutes total work time AS HOURS MINUTES AND SECONDS. Now to do money we must eventually get to some decimal values. Why?? because in excel 12 hours and 2 minutes is really 0.501388888 or about half a day.

OK for purposes of explanation lets say the formula above was in E7 and our 12:02 or 12:32 answer is there. To get decimal hours we use the HOUR and MIN formulas giving us the following

=HOUR(E7)+(MINUTE(E7)/60)

that should return 12.033 a true decimal value which is your hours value (lower case an not the HOUR in the formula above). plug this result into the formulas that I wrote in my first message and you should be good to go. Rates are obviously how much you are paying in currency and should work for you just fine now. Remember to set cell formats to the right thing in the right cells i.e. time, money or general numbers.

I hope I made no mistakes, too bad the board does not support file attaches to messages. I will try to check back later to see how you made out.

Excel Timesheet

Comments