Time sheet calculations

[[email protected]]

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

[[email protected]]

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

Bearacade wrote:
> 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 |
> +-------------------------------------------------------------------+
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=570513

[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

[email protected]
[email protected] with @tiscali.co.uk


<[email protected]> wrote in message
news:[email protected]...
> When I clicked on the link it gave me an invalid attachment error.
> Could you try it again?
> thanks
>
> Bearacade wrote:
>> 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 |
>> +-------------------------------------------------------------------+
>>
>> --
>> Bearacade
>>
>>
>> ------------------------------------------------------------------------
>> Bearacade's Profile:
>> http://www.excelforum.com/member.php...o&userid=35016
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=570513
>

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

[[email protected]]

I got it, and it works like a charm. Thank you very much for the help.
Bearacade wrote:
> 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?
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=570513

[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 someone starts work from 8 and leaves at 1, and then use comp from 1 - 5? I put these two entries in two seperate transactions and it seems to work 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 spreadsheet accordingly

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

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