Time sheet calculations

[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

 

[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 |
+-------------------------------------------------------------------+

 

[brianelowry]

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

 

[Sandy Mann]

Try following the "View this thread" like and then when you are in the Excel
forum click on the download there.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[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?

 

[brianelowry]

I got it, and it works like a charm. Thank you very much for the help.

 

[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

 

[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

 

[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

 

[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
+-------------------------------------------------------------------