calculating time with text
 

I am trying to get my spreadsheet to calculate hours paid for an
employee but I need it add overtime and subtact how late they were as
well. I have it in text format and need it to calculate in time
format. I am also trying to get it to read each cell. Is there any
way I can get to read each cell and calculate the time.


The formula you see here is what I am using.


COUNTIF(F6,"P*")/6 +
COUNTIF(G6:J6,"p*")/4.8 +
COUNTIF(G6:J6,"eh*")/4.8 +
COUNTIF(G6:J6,"mu*")/4.8 +
COUNTIF(G6:J6,"l*")/4.8 +
COUNTIF(G6:J6,"el*")/4.8 +
IF(RIGHT(G6,4) =3D "0:15",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) =3D "0:15",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) =3D "0:15",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) =3D "0:15",RIGHT(J6,4),0) +
IF(RIGHT(G6,4) =3D "0:30",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) =3D "0:30",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) =3D "0:30",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) =3D "0:30",RIGHT(J6,4),0) +
IF(RIGHT(G6,4) =3D "0:45",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) =3D "0:45",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) =3D "0:45",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) =3D "0:45",RIGHT(J6,4),0) +
IF(RIGHT(G6,4) =3D "0:60",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) =3D "0:60",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) =3D "0:60",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) =3D "0:60",RIGHT(J6,4),0) -
IF(OR(AND(LEFT(G6,1)=3D"L",RIGHT=AD(G6,4)=3D"0:08 ",RIGHT(G6,4)<"0:=AD23"),=
RIGHT(G6,4)=3D"0:15"),"0:15=AD",0)

-
IF(OR(AND(LEFT(G6,1)=3D"L",RIGHT=AD(G6,4)=3D"0:23 ",RIGHT(G6,4)<"0:=AD37"),=
RIGHT(G6,4)=3D"0:30"),"0:30=AD",0)

Hi!

Hmmm.....

That sure is an "interesting" way to keep a timesheet!

I have it in text format

What exactly does that mean?

COUNTIF(F6,"P*")

What are the COUNTIFs counting?

Can you post a sample of your data?

Biff

wrote in message
ups.com...
I am trying to get my spreadsheet to calculate hours paid for an
employee but I need it add overtime and subtact how late they were as
well. I have it in text format and need it to calculate in time
format. I am also trying to get it to read each cell. Is there any
way I can get to read each cell and calculate the time.


The formula you see here is what I am using.


COUNTIF(F6,"P*")/6 +
COUNTIF(G6:J6,"p*")/4.8 +
COUNTIF(G6:J6,"eh*")/4.8 +
COUNTIF(G6:J6,"mu*")/4.8 +
COUNTIF(G6:J6,"l*")/4.8 +
COUNTIF(G6:J6,"el*")/4.8 +
IF(RIGHT(G6,4) = "0:15",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) = "0:15",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) = "0:15",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) = "0:15",RIGHT(J6,4),0) +
IF(RIGHT(G6,4) = "0:30",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) = "0:30",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) = "0:30",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) = "0:30",RIGHT(J6,4),0) +
IF(RIGHT(G6,4) = "0:45",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) = "0:45",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) = "0:45",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) = "0:45",RIGHT(J6,4),0) +
IF(RIGHT(G6,4) = "0:60",RIGHT(G6,4),0) +
IF(RIGHT(H6,4) = "0:60",RIGHT(H6,4),0) +
IF(RIGHT(I6,4) = "0:60",RIGHT(I6,4),0) +
IF(RIGHT(J6,4) = "0:60",RIGHT(J6,4),0) -
IF(OR(AND(LEFT(G6,1)="L",RIGHT*(G6,4)="0:08",RIGH T(G6,4)<"0:*23"),RIGHT(G6,4)="0:15"),"0:15*",0)

-
IF(OR(AND(LEFT(G6,1)="L",RIGHT*(G6,4)="0:23",RIGH T(G6,4)<"0:*37"),RIGHT(G6,4)="0:30"),"0:30*",0)

count if are counting p representing that the associate was present.

Example 1

F G H I J

6 P P MU0:60 P P 25:00:00

Example 2

F G H I J

6 P P P P P 24:00:00

I have it to calculate the extra 60 minutes that you see in example 1
other wise it would calculate 24:00:00 as you see in example 2

What I need it to do is subtract if they leave early or if they are
late.

Hi!

Sorry, but I don't understand what you're trying to do with this.

While you may very well have a good reason to use this type of setup, I just
can't imagine doing a timesheet this way.

Look how long and complicated your formula is. It doesn't have to be that
way! Basically, timesheets are fairly simple and the formulas needed are
really simple.

I would highly recommend that you setup a "conventional" timesheet and use
normal times: ie: time in, time out.

I'd be more than glad to help you set it up, or you can search the internet
and find lots of free timesheet templates and sample files.

Biff

wrote in message
oups.com...
count if are counting p representing that the associate was present.

Example 1

F G H I J

6 P P MU0:60 P P 25:00:00

Example 2

F G H I J

6 P P P P P 24:00:00

I have it to calculate the extra 60 minutes that you see in example 1
other wise it would calculate 24:00:00 as you see in example 2

What I need it to do is subtract if they leave early or if they are
late.

I work in a call center and the formula i am coming up with is for our
attendance log in our new hire class. I need it to calculate in 15
minute increments. thanks for your help but i figured it out. I used
another sheet to do my calculations. if you want to see what i did let
me have your email address by emailing me at