Timesheet- Calculating

Hi... I'm trying to create a timesheet worksheet with the use of functions.

My goal is to extract (Regular, OT 1.5, OT 2, Vacations Days) out of a row
of cells. Below is a sample of my worksheet.

A B C D E F G H I J K L
1 Sun Mon Tue Wed Thu Fri Sat Reg Hours OT 1.5 OT 2 VP Days
2 Craig Off 8 8 8 8 8 Off 40 0 0 0
3 Bob Off 9 8 9 8 8 Off 40 1 0 0
4 John Off 8 12 8 VP 8 Off 32 3 1 1


I would like the cells in column "I" to add up the hours that are =<8 and to
ignor other values like VP, Off.
I would like the cells in column "J" to add the hours > 8 and =<11 also
ignoring other entries.
I would like the cells in column "K" to add the hours > 11 also ignoring
other entries..
I would like the cells in column "L" to count the VP's.

I'm using countif to count the VP's.

I was trying to use =Min() and =Max() to calculate the Reg and OT 1.5 but
using the =Min() function added 8 hours even for
days with no entries or the entry is "Off" or "VP". Using the =Max()
function I wasn't sure how extract the proper value for
OT 1.5 and OT 2.

Thank in Advance Again!
Craig

A B C D E F G H I J K L
1 Sun Mon Tue Wed Thu Fri Sat Reg Hours OT 1.5 OT 2 VP Days
2 Craig Off 8 8 8 8 8 Off 40 0 0 0
3 Bob Off 9 8 9 8 8 Off 40 1 0 0
4 John Off 8 12 8 VP 8 Off 32 3 1 1



Hopefully this formatted a bit better!

Craig



"Craig" <[email protected]> wrote in message news:FqByf.353299$ki.302940@pd7tw2no...
> Hi... I'm trying to create a timesheet worksheet with the use of functions.
>
> My goal is to extract (Regular, OT 1.5, OT 2, Vacations Days) out of a row
> of cells. Below is a sample of my worksheet.
>
> A B C D E F G H I J K L
> 1 Sun Mon Tue Wed Thu Fri Sat Reg Hours OT 1.5 OT 2 VP Days
> 2 Craig Off 8 8 8 8 8 Off 40 0 0 0
> 3 Bob Off 9 8 9 8 8 Off 40 1 0 0
> 4 John Off 8 12 8 VP 8 Off 32 3 1 1
>
>
> I would like the cells in column "I" to add up the hours that are =<8 and to
> ignor other values like VP, Off.
> I would like the cells in column "J" to add the hours > 8 and =<11 also
> ignoring other entries.
> I would like the cells in column "K" to add the hours > 11 also ignoring
> other entries..
> I would like the cells in column "L" to count the VP's.
>
> I'm using countif to count the VP's.
>
> I was trying to use =Min() and =Max() to calculate the Reg and OT 1.5 but
> using the =Min() function added 8 hours even for
> days with no entries or the entry is "Off" or "VP". Using the =Max()
> function I wasn't sure how extract the proper value for
> OT 1.5 and OT 2.
>
> Thank in Advance Again!
> Craig
>
>
>
>
>

"Craig" <[email protected]> wrote in message news:ofCyf.353891$ki.331130@pd7tw2no...

*******************************************************************
A B C D E F G H I J K L
1 Sun Mon Tue Wed Thu Fri Sat Reg Hours OT 1.5 OT 2 VP Days
2 Craig Off 8 8 8 8 8 Off 40 0 0 0
3 Bob Off 9 8 9 8 8 Off 40 1 0 0
4 John Off 8 12 8 VP 8 Off 32 3 1 1



Hopefully this formatted a bit better!

****************************************************************


Only if you don't read posts in plain text!



Shouldn't the names be in Column A? If so then it moves all the column letters step one place to the left.

Would a UDF Function be OK? if so try:

Reg Hours (new Column J) =Norm(B2:H2)
OT 1.5 =Eleven(B2:H2)
OT 2 =Twelve(B2:H2)

with the following Functions in a normal module:

Function Norm(Here As Range)
Application.Volatile
For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value <= 8 Then TotN = TotN + cell.Value
If cell.Value > 8 Then TotN = TotN + 8
Again:
Next cell
Norm = TotN
End Function



Function Eleven(Here As Range)
Application.Volatile

For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value > 8 Then TotE = TotE + Application.Min(3, cell.Value - 8)
Again:
Next cell
Eleven = TotE
End Function


Function Twelve(Here As Range)
Application.Volatile

For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value > 11 Then TotT = TotT + cell.Value - 11
Again:
Next cell
Twelve = TotT
End Function


Incidentally you are doing Bob out of an hour's overtime at time and a half <g>
--
HTH

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

I receive an error after I enter these functions. It says "Compile Error: Can't find project or Library"
It seems to have a problem with the For Each cell In Here
It doesn't like the word "cell"

Also I quickly created this example... yes the names should have been "A", but don't worry about poor old BOB, he's fictitious!

I have never heard the term UDF Function before... what exactly does that mean?

Craig

"Sandy Mann" <[email protected]> wrote in message news:u%[email protected]...
"Craig" <[email protected]> wrote in message news:ofCyf.353891$ki.331130@pd7tw2no...

*******************************************************************
A B C D E F G H I J K L
1 Sun Mon Tue Wed Thu Fri Sat Reg Hours OT 1.5 OT 2 VP Days
2 Craig Off 8 8 8 8 8 Off 40 0 0 0
3 Bob Off 9 8 9 8 8 Off 40 1 0 0
4 John Off 8 12 8 VP 8 Off 32 3 1 1



Hopefully this formatted a bit better!

****************************************************************


Only if you don't read posts in plain text!



Shouldn't the names be in Column A? If so then it moves all the column letters step one place to the left.

Would a UDF Function be OK? if so try:

Reg Hours (new Column J) =Norm(B2:H2)
OT 1.5 =Eleven(B2:H2)
OT 2 =Twelve(B2:H2)

with the following Functions in a normal module:

Function Norm(Here As Range)
Application.Volatile
For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value <= 8 Then TotN = TotN + cell.Value
If cell.Value > 8 Then TotN = TotN + 8
Again:
Next cell
Norm = TotN
End Function



Function Eleven(Here As Range)
Application.Volatile

For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value > 8 Then TotE = TotE + Application.Min(3, cell.Value - 8)
Again:
Next cell
Eleven = TotE
End Function


Function Twelve(Here As Range)
Application.Volatile

For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value > 11 Then TotT = TotT + cell.Value - 11
Again:
Next cell
Twelve = TotT
End Function


Incidentally you are doing Bob out of an hour's overtime at time and a half <g>
--
HTH

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

Hi Craig,

Starting off from with the easy one: UDF means User Defined Function


A Google search brought up many posts, almost all of which said that the reason was a missing reference. One such post was from Tom Ogilvy:

Start of Tom's post
***************************************************************
If you get this message, then if you look within the VBE at
Tools=>References, you should see at least one item showing MISSING. (Make
sure the workbook with the problem is the active workbook/project in the
vbe - click on it in the project explorer). If it is not necessary, uncheck
it. If it is, browse and find it.
*************************************************************
End of Chip's post

There was also a post from Chip Pearson that suggested re-registering Excel:

Start of Chip's Post:
**************************************************************
You can fix many of these sort of problems by reregistering Excel
with Windows. Close Excel, and then run it from the Windows Run
dialog on the Start menu. Include the /regserver switch. E.g.,
"C:\program files\microsoft office\office\excel.exe" /regserver

This causes Excel to start, rewrite all its keys into the Windows
registry, and then quit. This can cure many problems in Excel.

*******************************************************************

End of Chip's post

Obviously use your own path to Excel if it is different and note the space between the .exe" and the /regserver



There was also a post that suggested that the variable be DIMmed so I would try these suggestions in reverse order:

First try adding:

Dim Cell

immediately below the Function name

If that doesn't work try re-registering Excel and finally check to see if any of the references are "Missing"


--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk
"Craig" <[email protected]> wrote in message news:5GWyf.246905$2k.194090@pd7tw1no...
I receive an error after I enter these functions. It says "Compile Error: Can't find project or Library"
It seems to have a problem with the For Each cell In Here
It doesn't like the word "cell"

Also I quickly created this example... yes the names should have been "A", but don't worry about poor old BOB, he's fictitious!

I have never heard the term UDF Function before... what exactly does that mean?

Craig

"Sandy Mann" <[email protected]> wrote in message news:u%[email protected]...
"Craig" <[email protected]> wrote in message news:ofCyf.353891$ki.331130@pd7tw2no...

*******************************************************************
A B C D E F G H I J K L
1 Sun Mon Tue Wed Thu Fri Sat Reg Hours OT 1.5 OT 2 VP Days
2 Craig Off 8 8 8 8 8 Off 40 0 0 0
3 Bob Off 9 8 9 8 8 Off 40 1 0 0
4 John Off 8 12 8 VP 8 Off 32 3 1 1



Hopefully this formatted a bit better!

****************************************************************


Only if you don't read posts in plain text!



Shouldn't the names be in Column A? If so then it moves all the column letters step one place to the left.

Would a UDF Function be OK? if so try:

Reg Hours (new Column J) =Norm(B2:H2)
OT 1.5 =Eleven(B2:H2)
OT 2 =Twelve(B2:H2)

with the following Functions in a normal module:

Function Norm(Here As Range)
Application.Volatile
For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value <= 8 Then TotN = TotN + cell.Value
If cell.Value > 8 Then TotN = TotN + 8
Again:
Next cell
Norm = TotN
End Function



Function Eleven(Here As Range)
Application.Volatile

For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value > 8 Then TotE = TotE + Application.Min(3, cell.Value - 8)
Again:
Next cell
Eleven = TotE
End Function


Function Twelve(Here As Range)
Application.Volatile

For Each cell In Here
If Application.IsText(cell.Value) Then GoTo Again
If cell.Value > 11 Then TotT = TotT + cell.Value - 11
Again:
Next cell
Twelve = TotT
End Function


Incidentally you are doing Bob out of an hour's overtime at time and a half <g>
--
HTH

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