Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring time worked, and then separating it into regular time an.
This should be relatively simple, but I cant figure it out. The function has to be setup in one cell. First; the function needs to figure how many hours you worked from the beginning time to the ending time. Second; the hours worked has to be split into normal working hours and overtime. The hours an employee can work obviously varies between full and part-time employees, but there can not be a vlookup on the timesheet. Although a vlookup on another worksheet in an excel workbook is acceptable. Overtime hours will trickle off the remaining time into the next column. Finally; this is all based on quarter hours. So the entire function has to be rounded. I know how to use the round function, but, I can only use it in simple problems right now. Whenever someone has the time to help me; thanks. Don, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring time worked, and then separating it into regular time an.
For an 8 hour day
=MIN(ROUND((B1-A1)*96,0)/96,TIME(8,0,0)) gives regular time, and =MAX(0,ROUND((B1-A1)*96,0)/96-TIME(8,0,0)) gives overtime -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don" wrote in message ... This should be relatively simple, but I can't figure it out. The function has to be setup in one cell. First; the function needs to figure how many hours you worked from the beginning time to the ending time. Second; the hours worked has to be split into normal working hours and overtime. The hours an employee can work obviously varies between full and part-time employees, but there can not be a vlookup on the timesheet. Although a vlookup on another worksheet in an excel workbook is acceptable. Overtime hours will trickle off the remaining time into the next column. Finally; this is all based on quarter hours. So the entire function has to be rounded. I know how to use the round function, but, I can only use it in simple problems right now. Whenever someone has the time to help me; thanks. Don, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring time worked, and then separating it into regular time
Thanks for helping so quickly.
Sadly, I not only forgot to mention some variables, and I already solved my own problem while waiting for someone to reply. The formulas are all more complicated, but all work perfectly. I am actually going to try and post them in a reasonable fashion for others. Timesheet columns are as follows from column A I. A Day (Monday, Tuesday, etc.) B Date (10-19-06) C Start Time (h:mm AM/PM) D End Time (h:mm AM/PM) E Regular Hours =IF((TEXT(((D12-C12)-G12),"h:mm"))"8:00",("8:00"),(TEXT(((D12-C12)-G12),"h:mm"))) F Overtime Hours =IF(E12="8:00",TEXT((((D12-C12)-G12)-E12),"h:mm"),"0:00") G Peronsal/Lunch (h:mm) H Vacation (useless really since vacation is measured in days) I Day Totals Work Hours =IF(TEXT((E12+F12-H12),"h:mm")="0:00","",TEXT((E12+F12-H12),"h:mm")) Now I have another problem, and I think it is caused by all the TEXT( in my formulas. I try to auto-sum the hours for the week, but it always comes up as 0:00 (h:mm). I need this to show the totals for not only all the total days, but the totals for columns E H. This time I need someone to tell me what to change in my formulas without changing them entirely. These formulas do exactly what they need to do for the full-time employee timesheet. Not only that, but I understand everything just by looking at them. So any help would be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring time worked, and then separating it into regular time
Your formulae don't work for me, and you don't round to the quarter hour as
originally asked for. Try these in E12:F12 =MIN(ROUND((D12-C12-G12)*96,0)/96,TIME(8,0,0)) =MAX(0,ROUND((D12-C12-G12)*96,0)/96-TIME(8,0,0)) format them as time, and they will sum okay. Remember to use a format of [h]:mm in the totals, to allow more than 24 hours. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don" wrote in message ... Thanks for helping so quickly. Sadly, I not only forgot to mention some variables, and I already solved my own problem while waiting for someone to reply. The formulas are all more complicated, but all work perfectly. I am actually going to try and post them in a reasonable fashion for others. Timesheet columns are as follows from column A - I. A - Day (Monday, Tuesday, etc.) B - Date (10-19-06) C - Start Time (h:mm AM/PM) D - End Time (h:mm AM/PM) E - Regular Hours =IF((TEXT(((D12-C12)-G12),"h:mm"))"8:00",("8:00"),(TEXT(((D12-C12)-G12),"h: mm"))) F - Overtime Hours =IF(E12="8:00",TEXT((((D12-C12)-G12)-E12),"h:mm"),"0:00") G - Peronsal/Lunch ("h:mm") H - Vacation (useless really since vacation is measured in days) I - Day Totals Work Hours =IF(TEXT((E12+F12-H12),"h:mm")="0:00","",TEXT((E12+F12-H12),"h:mm")) Now I have another problem, and I think it is caused by all the "TEXT(" in my formulas. I try to auto-sum the hours for the week, but it always comes up as 0:00 ("h:mm"). I need this to show the totals for not only all the total days, but the totals for columns E - H. This time I need someone to tell me what to change in my formulas without changing them entirely. These formulas do exactly what they need to do for the full-time employee timesheet. Not only that, but I understand everything just by looking at them. So any help would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring time worked, and then separating it into regular time
Bob,
Since you seem to be the only one helping me, and by the looks of it everyone else, thanks again. You were right about my formulas. And yours worked almost perfectly when I first used them. 3 seconds later after formatting the cells the way you told me to, it worked perfectly. Problem is there is something I was not told to compensate for until earlier today. And dont ask me why they want this change, but they do. I will try and make this quick and simple to save on reading time. What the time sheet does right now. 8 AM 4:30 PM Half an hour lunch = 8 hours 0 overtime 8 AM 5 PM Half an hour lunch = 8 hours and Β½ overtime 8 AM 4 PM Half an hour lunch = 7 Β½ hours 0 overtime What they are asking for. 8 AM 4:30 PM Half an hour lunch = 8 hours 0 overtime 8 AM 5 PM Half an hour lunch = 8 hours 0 overtime 8 AM 4 PM Half an hour lunch = 8 hours 0 overtime They want any overtime from one day to fill voids where they worked fewer hours to make up for overtime. This could be the day after or at the end of the week when they make-up for the overtime. To add to this problem, this has to work bi-weekly timesheet. So if someone does work over 40 hours one week, overtime will not spill into the next weeks empty spots. Incase the person is on vacation or sick. So on a timesheet with 14 spots for hours worked, the top 7 can not interfere with the bottom 7, but still auto-sum at the end. BTW: Sorry I keep throwing things out there bit by bit, but these people cant seem to finish a thought. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring time worked, and then separating it into regular time
Don,
Rather than calculate on a daily basis, is it okay just to run a weekly total, for regular and overtime hours? That would be a lot simpler. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don" wrote in message ... Bob, Since you seem to be the only one helping me, and by the looks of it everyone else, thanks again. You were right about my formulas. And yours worked almost perfectly when I first used them. 3 seconds later after formatting the cells the way you told me to, it worked perfectly. Problem is there is something I was not told to compensate for until earlier today. And don't ask me why they want this change, but they do. I will try and make this quick and simple to save on reading time. What the time sheet does right now. 8 AM - 4:30 PM Half an hour lunch = 8 hours 0 overtime 8 AM - 5 PM Half an hour lunch = 8 hours and ½ overtime 8 AM - 4 PM Half an hour lunch = 7 ½ hours 0 overtime What they are asking for. 8 AM - 4:30 PM Half an hour lunch = 8 hours 0 overtime 8 AM - 5 PM Half an hour lunch = 8 hours 0 overtime 8 AM - 4 PM Half an hour lunch = 8 hours 0 overtime They want any overtime from one day to fill voids where they worked fewer hours to make up for overtime. This could be the day after or at the end of the week when they make-up for the overtime. To add to this problem, this has to work bi-weekly timesheet. So if someone does work over 40 hours one week, overtime will not spill into the next weeks empty spots. Incase the person is on vacation or sick. So on a timesheet with 14 spots for hours worked, the top 7 can not interfere with the bottom 7, but still auto-sum at the end. BTW: Sorry I keep throwing things out there bit by bit, but these people can't seem to finish a thought. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring time worked, and then separating it into regular time
Might help if I posted them.
In day 7 of week 1, add =MIN(SUMPRODUCT(ROUND((D12:D18-C12:C18-G12:G18)*96,0)/96),2+TIME(8,0,0)) and =MAX(0,SUMPRODUCT(ROUND((D12:D18-C12:C18-G12:G18)*96,0)/96)-(2+TIME(8,0,0))) and copy/paste to day 7 of week 2. One question here though is the week 56 hours or 40? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Don, Rather than calculate on a daily basis, is it okay just to run a weekly total, for regular and overtime hours? That would be a lot simpler. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don" wrote in message ... Bob, Since you seem to be the only one helping me, and by the looks of it everyone else, thanks again. You were right about my formulas. And yours worked almost perfectly when I first used them. 3 seconds later after formatting the cells the way you told me to, it worked perfectly. Problem is there is something I was not told to compensate for until earlier today. And don't ask me why they want this change, but they do. I will try and make this quick and simple to save on reading time. What the time sheet does right now. 8 AM - 4:30 PM Half an hour lunch = 8 hours 0 overtime 8 AM - 5 PM Half an hour lunch = 8 hours and ½ overtime 8 AM - 4 PM Half an hour lunch = 7 ½ hours 0 overtime What they are asking for. 8 AM - 4:30 PM Half an hour lunch = 8 hours 0 overtime 8 AM - 5 PM Half an hour lunch = 8 hours 0 overtime 8 AM - 4 PM Half an hour lunch = 8 hours 0 overtime They want any overtime from one day to fill voids where they worked fewer hours to make up for overtime. This could be the day after or at the end of the week when they make-up for the overtime. To add to this problem, this has to work bi-weekly timesheet. So if someone does work over 40 hours one week, overtime will not spill into the next weeks empty spots. Incase the person is on vacation or sick. So on a timesheet with 14 spots for hours worked, the top 7 can not interfere with the bottom 7, but still auto-sum at the end. BTW: Sorry I keep throwing things out there bit by bit, but these people can't seem to finish a thought. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|