Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMPLOYEE WEEKLY SCHEDULE
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - EMPLOYEE WEEKLY SCHEDULE
I'm sorry I guess I forgot to ADD this to the above question I made it is for
Excel 2002 "Timithesis" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - EMPLOYEE WEEKLY SCHEDULE
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 "Timithesis" wrote: I'm sorry I guess I forgot to ADD this to the above question I made it is for Excel 2002 "Timithesis" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - EMPLOYEE WEEKLY SCHEDULE
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" wrote: I'm sorry I guess I forgot to ADD this to the above question I made it is for Excel 2002 "Timithesis" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - EMPLOYEE WEEKLY SCHEDULE
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - EMPLOYEE WEEKLY SCHEDULE
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) "Timithesis" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - EMPLOYEE WEEKLY SCHEDULE
If it comes back negative then you have a start time before an end time, eg
start at 1pm, finish at 11am. "Timithesis" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a weekly rotating employee work schedule | Excel Discussion (Misc queries) | |||
Employee schedule | Excel Discussion (Misc queries) | |||
employee schedule | Excel Discussion (Misc queries) | |||
Employee Schedule | Excel Programming | |||
How do I create an Employee weekly scheduler | Excel Worksheet Functions |