Timesheet- Calculating

C

Craig

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
 
C

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
 
S

Sandy Mann

*******************************************************************
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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
C

Craig

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


*******************************************************************
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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
S

Sandy Mann

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
(e-mail address removed)
(e-mail address removed) 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


*******************************************************************
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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top