Time sheet calculations

B

brianelowry

I've got a timesheet built, but it does something slightly unique that
I haven't been able to find an example of or that someone has already
asked this question, so here it goes.

First, my worksheet has.
Column A
Date
Column B
Start time
Column C
End time
Column D
Code (R-regular time, C- comp time, V-vacation, etc)
Column E
Hours (C2-B2)*24
Column F
Comp Time Earned =IF(E2>8,SUM(E2-8),0)

What I need to do is to have the total of Comp time earned be deducted
if the Code in Column D is "C". I've tried with
=IF(D2="C",SUM(F33-E2),0) and it seems to work okay looking at a
single cell, but I need to have it look at the whole column and add in
a cell for comp time used.
Does what I have so far seem like the best way to do it?
What should I use to look at the entire column?
Better question, does all of this make sense?

Thanks in advance.
Brian
 
B

Bearacade

I am not sure if this is right. But I have attached a sample worksheet
for you to look at.

It list out how much comp they have accumulated, how much they have
used, how much they have left. It also tells you if they used comp
that they haven't earned yet..

HTH


+-------------------------------------------------------------------+
|Filename: comp.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5177 |
+-------------------------------------------------------------------+
 
B

brianelowry

When I clicked on the link it gave me an invalid attachment error.
Could you try it again?
thanks
 
B

Bearacade

Did you get the download to work? If not, I can send you an email.

If it worked, is that what you are looking for?
 
B

brianlowry

I ran into one small snag today.

What i found out is that my comp time isn't accurately calculated if
someone works less than 8 hours.
For example, if I work 4 hours, then use 4 hours comp time, my totals
aren't accurate.
My Comp time earned is figured this this IF statement
=IF(D8="Regular", IF(E8>8,SUM(E8-8),0), 0)

Can I modify this to find the difference of the value if it's less than
8 hours and add that into Column G for Comp Time used?

When I get this deep into functions, my head beings to spin.. :)
Thanks for the help!

Brian
 
B

Bearacade

Hey Brian,

I am unclear what you mean by less than 8 hours and comp. If someon
starts work from 8 and leaves at 1, and then use comp from 1 - 5?
put these two entries in two seperate transactions and it seems to wor
ok. Perhaps you can clarify
 
B

brianlowry

You're correct in a way.
What I'm looking for is for example
August 11-
8:00am - 11:30 am = 3.5 and code this as R
Then I would have to have a second entry for that same date that
figures the comp time.
So this one entry would figure 3.5 hours of regular time and 4.5 of
comp time used.
Does that make sense?

Thanks
Brian
 
B

Bearacade

It does and I included a new spreadsheet, HTHs.

There may be problems though... and you have to adjust the spreadshee
accordingly

I set it up so that if they work for more than 6 hours, it takes a
hour lunch out, so from 8AM - 5PM, It's a 8 hour shift with an hou
lunch

There are probably other things you will come up with, don't hesitat
to ask

+-------------------------------------------------------------------
|Filename: comp1.zip
|Download: http://www.excelforum.com/attachment.php?postid=5194
+-------------------------------------------------------------------
 
Top