Please help me with this time card setup.

[_pub01]

Hi folks,

I found a time card setup described on another forum and have tried to
make it work for me. I have run into problems so I sent an email to the
original poster. The thing is, his post was from a couple of years ago
and I think his email may be no longer valid. Could you look over my
email to him, posted, below, and help me answer my questions?

"Gary,

I found your post on Exceltip.com describing your "favorite timesheet."
You said:

My favorite timesheet has 9 columns A:I.

Col A: Wk# =WEEKNUM(C3)

Col B: Day =CHOOSE(MOD(C3,7)
+1,"Sat","Sun","Mon","Tue","Wed","Thu","Fri")

Col C: Date =IF(OR(MOD(C3,7)&lt2,MOD(C3,7)=6),C3+CHOOSE(MOD(C3,7)+1,2,1,0,6,5,4,3),C3+1)

Col D: Start [direct input] 7:48:00 AM

Col E: End [direct input] 5:45:00 PM

Col F: Lunch [direct input] .75

Col G: Worked Time =(MOD((TIME(HOUR(E3),MROUND(MINUTE(E3),15),0)-TIME(HOUR(D3),MROUND(MINUTE(D3),15),0)),1)*24)-F3

Col H: Cum Hrs/Wk =IF(A3<&gtA4,SUMIF(A:A,A3,G:G),"")

Col I: Notes: [direct input] Any notes to explain variances in schedule

This format rounds to the nearest 15 minutes [Col G] and runs Monday thru Friday [Col C]. If you work Saturday or Sunday, simply entering the correct date in Col C (overriding the formula) will adjust the Wk # and Day.
This format also takes working past midnight into account.
It does NOT allow for working more than 24 hours in a shift.

Row 2 typically needs to have Col A and Col C as direct input. From row 3 and down, the formulas handle all info in Col A, B, C, G, H.

Hope this thread helps.
Sincerely,
Gary Brown

I am an Excel novice so it should come as no surprise that I have spent
the entire workday trying to get this to work for me. The problem may
be that I am trying to modify your instructions to work with a seven
day workweek starting on Thursday. A few specific issues I have are:

1) In your instructions for column C you write &lt2. I am assuming you
mean 'less than' so I have written it as <2 in the formula and it seems
to work. Is there a reason you wrote it that way? The same issue comes
up in your instructions for column H where you say A3<&gtA4. Here, I am
really lost as I can't find any translation for that series of
operators. Do you mean 'greater than,' 'less than,' or what?

2) In column C, on which the whole table seems to build, what dictates
the start of the week and the number of days? If I could figure that
out I could probably modify the table to meet my needs but, for the
life of me, I can't figure out where that info is hidden in the
calculation. I have tried a trial and error method plugging in values
and that, also, leaves me bewildered. Why, for instance, does Monday
9/4/06 know to come after Friday 9/1/06? According to the formulas as I
understand them the date calculation for that day would be 9/2/06.

Any help you could provide would be greatly appreciated!

Sincerely,

Evan"

Anybody?

Thanks in advance!

Evan

 

[PhilD]

up in your instructions for column H where you say A3<&gtA4. Here, I am
really lost as I can't find any translation for that series of
operators. Do you mean 'greater than,' 'less than,' or what?

A3<>A4 In other words, A3 is less than or greater than A4, or A3 is
not equal to A4.

Hope this helps.

PhilD

 

[_pub01]

A3<>A4 In other words, A3 is less than or greater than A4, or A3 is
not equal to A4.

Hope this helps.

PhilD

Since a time card will always move forward chronologically, wouldn't
"less than" be the only necessary comparison? I mean, this if statemnt
is just checking whether the week number has changed, no?

Thanks for the help!