Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
I am fairly new to excel and formulas and I am attempting to write a formula
that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
Hi Mike:
You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
Martin:
Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9, and 1-10. Those could be all the 8 hour shifts, but then there could be 7 hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would have to recognize all the shifts and understand how many hours each shift was and then try to total them. I am able to understand a basic IF(A2="8-5", 8, 0) statement. But being able to put in all the cases is proving more difficult for me! Thanks for your help! Mike "Martin Fishlock" wrote: Hi Mike: You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
Why don't you record the start-time and finish-time for each day, and
then just subtract one from the other to give the number of hours for that day? That implies using 3 columns for each day. Hope this helps. Pete On Jan 30, 12:23 am, mittymun wrote: Martin: Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9, and 1-10. Those could be all the 8 hour shifts, but then there could be 7 hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would have to recognize all the shifts and understand how many hours each shift was and then try to total them. I am able to understand a basic IF(A2="8-5", 8, 0) statement. But being able to put in all the cases is proving more difficult for me! Thanks for your help! Mike "Martin Fishlock" wrote: Hi Mike: You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
yes that was great help, now how do I write an if statement where if the
shift is over 6.5 hours, than I need to subtract a full hour for the lunch? Mike "Pete_UK" wrote: Why don't you record the start-time and finish-time for each day, and then just subtract one from the other to give the number of hours for that day? That implies using 3 columns for each day. Hope this helps. Pete On Jan 30, 12:23 am, mittymun wrote: Martin: Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9, and 1-10. Those could be all the 8 hour shifts, but then there could be 7 hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would have to recognize all the shifts and understand how many hours each shift was and then try to total them. I am able to understand a basic IF(A2="8-5", 8, 0) statement. But being able to put in all the cases is proving more difficult for me! Thanks for your help! Mike "Martin Fishlock" wrote: Hi Mike: You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike- Hide quoted text -- Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
Another way to do it is with data validation and vlookup.
You have the data validation list in say cell a1 which is a list and refers to cells f1:f6 = '8-5, '9-6, '10-7, '11-8, '12-9, '1-10 and in g1:g6 = 8,8,8,8,8,8 then in cell b2 =vlookup(a1,$f$1:$g$6,2,false) to give the hours works. But I think that Pete's suggestion is the easiest as you did not mention about overtime if it needs to be considered. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Pete_UK" wrote: Why don't you record the start-time and finish-time for each day, and then just subtract one from the other to give the number of hours for that day? That implies using 3 columns for each day. Hope this helps. Pete On Jan 30, 12:23 am, mittymun wrote: Martin: Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9, and 1-10. Those could be all the 8 hour shifts, but then there could be 7 hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would have to recognize all the shifts and understand how many hours each shift was and then try to total them. I am able to understand a basic IF(A2="8-5", 8, 0) statement. But being able to put in all the cases is proving more difficult for me! Thanks for your help! Mike "Martin Fishlock" wrote: Hi Mike: You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike- Hide quoted text -- Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
If the above cells are formated as time:
=if(A1=(6.5/24),-1/24,0) or =if(A1=(6.5/24),A1-1/24,a1) if they are numbers don't divide by 24 -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: yes that was great help, now how do I write an if statement where if the shift is over 6.5 hours, than I need to subtract a full hour for the lunch? Mike "Pete_UK" wrote: Why don't you record the start-time and finish-time for each day, and then just subtract one from the other to give the number of hours for that day? That implies using 3 columns for each day. Hope this helps. Pete On Jan 30, 12:23 am, mittymun wrote: Martin: Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9, and 1-10. Those could be all the 8 hour shifts, but then there could be 7 hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would have to recognize all the shifts and understand how many hours each shift was and then try to total them. I am able to understand a basic IF(A2="8-5", 8, 0) statement. But being able to put in all the cases is proving more difficult for me! Thanks for your help! Mike "Martin Fishlock" wrote: Hi Mike: You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike- Hide quoted text -- Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
Alright,
Well I started to do it this way, and it ended up working fine....now all I need to do is find out how to embed another if statement into it that considers an input "off" and would put a 0 for the number of hours worked. Any idea? Thanks again, Mike "Martin Fishlock" wrote: Another way to do it is with data validation and vlookup. You have the data validation list in say cell a1 which is a list and refers to cells f1:f6 = '8-5, '9-6, '10-7, '11-8, '12-9, '1-10 and in g1:g6 = 8,8,8,8,8,8 then in cell b2 =vlookup(a1,$f$1:$g$6,2,false) to give the hours works. But I think that Pete's suggestion is the easiest as you did not mention about overtime if it needs to be considered. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Pete_UK" wrote: Why don't you record the start-time and finish-time for each day, and then just subtract one from the other to give the number of hours for that day? That implies using 3 columns for each day. Hope this helps. Pete On Jan 30, 12:23 am, mittymun wrote: Martin: Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9, and 1-10. Those could be all the 8 hour shifts, but then there could be 7 hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would have to recognize all the shifts and understand how many hours each shift was and then try to total them. I am able to understand a basic IF(A2="8-5", 8, 0) statement. But being able to put in all the cases is proving more difficult for me! Thanks for your help! Mike "Martin Fishlock" wrote: Hi Mike: You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike- Hide quoted text -- Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scheduling
sorry, forgot to show my example:
=IF(HOUR(D8-C8)6, HOUR(D8-C8)-1, HOUR(D8-C8)) so if there was an hour specified, it is calculating it fine, but now what if the cell says "off", I need it to tell me that there are 0(zero) hours for that day...nesting if statement? Thanks again! "mittymun" wrote: Alright, Well I started to do it this way, and it ended up working fine....now all I need to do is find out how to embed another if statement into it that considers an input "off" and would put a 0 for the number of hours worked. Any idea? Thanks again, Mike "Martin Fishlock" wrote: Another way to do it is with data validation and vlookup. You have the data validation list in say cell a1 which is a list and refers to cells f1:f6 = '8-5, '9-6, '10-7, '11-8, '12-9, '1-10 and in g1:g6 = 8,8,8,8,8,8 then in cell b2 =vlookup(a1,$f$1:$g$6,2,false) to give the hours works. But I think that Pete's suggestion is the easiest as you did not mention about overtime if it needs to be considered. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Pete_UK" wrote: Why don't you record the start-time and finish-time for each day, and then just subtract one from the other to give the number of hours for that day? That implies using 3 columns for each day. Hope this helps. Pete On Jan 30, 12:23 am, mittymun wrote: Martin: Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9, and 1-10. Those could be all the 8 hour shifts, but then there could be 7 hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would have to recognize all the shifts and understand how many hours each shift was and then try to total them. I am able to understand a basic IF(A2="8-5", 8, 0) statement. But being able to put in all the cases is proving more difficult for me! Thanks for your help! Mike "Martin Fishlock" wrote: Hi Mike: You could try using additional columns for each day to make the calculations easier to understand for each day and then add the cells together. You could write a macro that handles the process. But you really need to explain the conditions to get some specific help. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "mittymun" wrote: I am fairly new to excel and formulas and I am attempting to write a formula that when it takes in a retail shift for Sunday through Saturday, it takes the amount of hours worked and totals it in a different cell. The main problem that I see is that there are a lot of conditions that could be met. Is there anything I can do to approach this? Thanks, Mike- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel date scheduling not msProject | Excel Worksheet Functions | |||
Can anyone suggest a good scheduling template for weekly shifts? | Excel Discussion (Misc queries) | |||
scheduling calendar | Excel Discussion (Misc queries) | |||
Excel 2003, time scheduling? | Excel Worksheet Functions | |||
I need a template for weekly scheduling of golf tee times | Charts and Charting in Excel |