 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008EMPLOYEE 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 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 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 > I'm sorry I guess I forgot to ADD this to the above question I made it is for > Excel 2002 [quoted text clipped - 39 lines] > (((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, 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 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.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Sam, > I formatted the cells B4:O4 as "h:mm" using the "Category-Custom" option; [quoted text clipped - 34 lines] > formula!! > that's a close as I seem to get..........H..E..L..P If it comes back negative then you have a start time before an end time, eg start at 1pm, finish at 11am. > Sam, > I formatted the cells B4:O4 as "h:mm" using the "Category-Custom" option; [quoted text clipped - 29 lines] > 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 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 > I'm sorry I guess I forgot to ADD this to the above question I made it is for > Excel 2002 [quoted text clipped - 39 lines] > (((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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.