EMPLOYEE WEEKLY SCHEDULE

[Timithesis]

OK, I have tried multiple ways of creating a schedule for my employees and I
have looked at multiple POSTS, but the one I think might of helped me I can't
find again.
So, please help me finally figure this (probibly easy formula for you, just
not me).


A4 is the employee name

B4 is Sunday ""IN" time OFF
C4 is Sunday "OUT" time OFF

D4 is Monday "IN" time OFF
E4 is Monday "OUT" time OFF

F4 is Tuesday ""IN" time OFF
G4 is Tuesday "OUT" time OFF

H4 is Wednesday "IN" time OFF
I4 is Wednesday "OUT" time OFF

J4 is Thursday ""IN" time 4:30 PM
K4 is Thursday "OUT" time 12:30 AM

L4 is Friday "IN" time 5:00 PM
M4 is Friday "OUT" time 1:00 AM

N4 is Saturday ""IN" time 1:00 PM
O4 is Saturday "OUT" time 5:30 AM

P4 is TOTAL time worked for week B4 thru O4 (to also adjust for 30 minute
lunch for any shift greater then 5 hours). Note: employee "OFF" Sunday thru
Wednesday (I plan on leaving the "OFF" days blank or empty)

B4 thru O4 should be "cell formatted) as ??
General?? Time?? Custom h:mm AM/PM?? or something else??

I know I am close to figuring this out, but I am seeking the final formula!!
(((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))*24)
that's a close as I seem to get..........H..E..L..P

 

[Timithesis]

I'm sorry I guess I forgot to ADD this to the above question I made it is for
Excel 2002

:

OK, I have tried multiple ways of creating a schedule for my employees and I
have looked at multiple POSTS, but the one I think might of helped me I can't
find again.

So, please help me finally figure this (probibly easy formula for you, just
not me).

A4 is the employee name

B4 is Sunday ""IN" time OFF
C4 is Sunday "OUT" time OFF

D4 is Monday "IN" time OFF
E4 is Monday "OUT" time OFF

F4 is Tuesday ""IN" time OFF
G4 is Tuesday "OUT" time OFF

H4 is Wednesday "IN" time OFF
I4 is Wednesday "OUT" time OFF

J4 is Thursday ""IN" time 4:30 PM
K4 is Thursday "OUT" time 12:30 AM

L4 is Friday "IN" time 5:00 PM
M4 is Friday "OUT" time 1:00 AM

N4 is Saturday ""IN" time 1:00 PM
O4 is Saturday "OUT" time 5:30 AM

P4 is TOTAL time worked for week B4 thru O4 (to also adjust for 30 minute
lunch for any shift greater then 5 hours). Note: employee "OFF" Sunday thru
Wednesday (I plan on leaving the "OFF" days blank or empty)

B4 thru O4 should be "cell formatted) as ??
General?? Time?? Custom h:mm AM/PM?? or something else??

I know I am close to figuring this out, but I am seeking the final formula!!
(((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))*24)
that's a close as I seem to get..........H..E..L..P

 

[Sam Wilson]

I wouldn't format it as any sort of time! The first half of your formula
(before *24) will give you a number equal to the number of days worked, so
including the second half of your formula will give you the number of hours.

Format B4:O4 as "HH:MM", then if P4 was your calculation have the following:
Q4: =int(P4)
R4: =(P4-Q4 )*60

And format Q4:R4 as number (no decimal places) These are then hours &
minutes worked. Hope that helps?

Sam

 

[FloMM2]

Timithesis,
What I woul suggest is to add a column between Name and the day of week,
since your timesheet crosses over to the next day.
In put date for every entry if it changes.
I put this formula in "Q5":
=IF(((24*(B5-B4)+(24*(L5-L4))))>5,SUM(((24*(B5-B4)+(24*(L5-L4))))+0.5,((24*(B5-B4)+(24*(B5-B4)))))
You will have to add another line for time out.
Column A Column B Column C thru Column P Column Q
Name Date IN ............................
Date OUT........................... Formula
hth , Good luck
Dennis

 

[Timithesis]

Sam,
I formatted the cells B4:O4 as "h:mm" using the "Category-Custom" option;
I did make Q4: =int(P4) & R4: =(P4-Q4 )*60
And I cell formatted Q4:R4 as a number (no decimal places)

P4 is the calculation, but I don't think it's the right formula (because it
doesn't work); the little popup says "Negative dates or times are displayed
as ####".

Should there be something prior to the formula such as :
=SUM(((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))*24)
=TIMEVALUE(((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))*24)
=TIME(((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))*24)
=HOUR(((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))*24)

The number that I should see in P4 is 18 the number I get now is negative 28

thanks for trying to help me though, do you have any other ideas??

=====================================================

P4 needs to be the TOTAL HOURS worked for week B4 thru O4 (to also adjust
for 30 minute
lunch for any shift greater then 5 hours). Note: employee "OFF" Sunday thru
Wednesday (I plan on leaving the "OFF" days blank or empty)

B4 thru O4 should be "cell formatted) as ??
General?? Time?? Custom h:mm AM/PM?? or something else??

(((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))*24)

I know I am close to figuring this out, but I am seeking the final formula!!
that's a close as I seem to get..........H..E..L..P

 

[Bob Phillips]

I am not clear why you get 18 hours, my calculation says 32.5.

This calculates that, format the result as [h]:mm

=SUM(MOD(IF(MOD(COLUMN(C4:O4),2)=1,C4:O4)-IF(MOD(COLUMN(B4:N4),2)=0,B4:N4),1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sam Wilson]

If it comes back negative then you have a start time before an end time, eg
start at 1pm, finish at 11am.