Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
I am trying to make a time sheet for everyone to be able to use in the
office. I want it to where when the total in Regular working hours reaches 37.5 it will put the excess working hours into another cell called of course Overtime. My dilema is not only this it is also that there are 2 colums of overtime. There is overtime for 37.5 hrs to 40 and then there is a column for 40+ hrs. Im so puzzled and confused I dont know if this is even going to be possible or how to even go about setting it up in a way that it WILL work. If anyone can please help me that would be so wonderful! If you want to see the document I have put together so far I can email it to you. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
Using A1 as the cell containing the total hours worked: For the first part of the OT use: =IF(A137.5,A1-37.5,"") For the second part of the OT use: =IF(A140,A1-40,"") -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=527581 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
=MIN(MAX(num_hours-37.5,0),2.5)
for hours between 37.5 and 40 =MAX(num_hours-40,0) for hours over 40 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... I am trying to make a time sheet for everyone to be able to use in the office. I want it to where when the total in Regular working hours reaches 37.5 it will put the excess working hours into another cell called of course Overtime. My dilema is not only this it is also that there are 2 colums of overtime. There is overtime for 37.5 hrs to 40 and then there is a column for 40+ hrs. Im so puzzled and confused I dont know if this is even going to be possible or how to even go about setting it up in a way that it WILL work. If anyone can please help me that would be so wonderful! If you want to see the document I have put together so far I can email it to you. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
I'm not quite sure what to do here, I am new to the advanced formulas in
excel. I dont want the employees to have to enter anything but their time. So in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in "num_hours"? Ok lets say that their regular hours total 52.50 which is obviously 15 hrs of overtime but there is going to be 37.5-40 hrs of overtime which is only 2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to where the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs will hit cell "a2" automatically? I am not sure if I am explaining this clear enough but I dont know how else to say it. Please Help!!! "Bob Phillips" wrote: =MIN(MAX(num_hours-37.5,0),2.5) for hours between 37.5 and 40 =MAX(num_hours-40,0) for hours over 40 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... I am trying to make a time sheet for everyone to be able to use in the office. I want it to where when the total in Regular working hours reaches 37.5 it will put the excess working hours into another cell called of course Overtime. My dilema is not only this it is also that there are 2 colums of overtime. There is overtime for 37.5 hrs to 40 and then there is a column for 40+ hrs. Im so puzzled and confused I dont know if this is even going to be possible or how to even go about setting it up in a way that it WILL work. If anyone can please help me that would be so wonderful! If you want to see the document I have put together so far I can email it to you. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
num_hours refers to the number of hours worked, 52.5 in your example.
Assuming that the hours are in a cell, let's say A1, you just add those formulae to A2 and A3 =MIN(MAX(A1-37.5,0),2.5) =MAX(A1-40,0) When your use puts their time in A1, the others autonmatically calculate HTH MigraineReliever "GettingaHeadache" wrote in message ... I'm not quite sure what to do here, I am new to the advanced formulas in excel. I dont want the employees to have to enter anything but their time. So in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in "num_hours"? Ok lets say that their regular hours total 52.50 which is obviously 15 hrs of overtime but there is going to be 37.5-40 hrs of overtime which is only 2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to where the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs will hit cell "a2" automatically? I am not sure if I am explaining this clear enough but I dont know how else to say it. Please Help!!! "Bob Phillips" wrote: =MIN(MAX(num_hours-37.5,0),2.5) for hours between 37.5 and 40 =MAX(num_hours-40,0) for hours over 40 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... I am trying to make a time sheet for everyone to be able to use in the office. I want it to where when the total in Regular working hours reaches 37.5 it will put the excess working hours into another cell called of course Overtime. My dilema is not only this it is also that there are 2 colums of overtime. There is overtime for 37.5 hrs to 40 and then there is a column for 40+ hrs. Im so puzzled and confused I dont know if this is even going to be possible or how to even go about setting it up in a way that it WILL work. If anyone can please help me that would be so wonderful! If you want to see the document I have put together so far I can email it to you. Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
Ahh! I got it!!! Now my new task is how to have it calculate their leave and
vaction time into their hours.. Any suggestions?! You are a ton of help! Thanks! "Bob Phillips" wrote: num_hours refers to the number of hours worked, 52.5 in your example. Assuming that the hours are in a cell, let's say A1, you just add those formulae to A2 and A3 =MIN(MAX(A1-37.5,0),2.5) =MAX(A1-40,0) When your use puts their time in A1, the others autonmatically calculate HTH MigraineReliever "GettingaHeadache" wrote in message ... I'm not quite sure what to do here, I am new to the advanced formulas in excel. I dont want the employees to have to enter anything but their time. So in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in "num_hours"? Ok lets say that their regular hours total 52.50 which is obviously 15 hrs of overtime but there is going to be 37.5-40 hrs of overtime which is only 2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to where the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs will hit cell "a2" automatically? I am not sure if I am explaining this clear enough but I dont know how else to say it. Please Help!!! "Bob Phillips" wrote: =MIN(MAX(num_hours-37.5,0),2.5) for hours between 37.5 and 40 =MAX(num_hours-40,0) for hours over 40 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... I am trying to make a time sheet for everyone to be able to use in the office. I want it to where when the total in Regular working hours reaches 37.5 it will put the excess working hours into another cell called of course Overtime. My dilema is not only this it is also that there are 2 colums of overtime. There is overtime for 37.5 hrs to 40 and then there is a column for 40+ hrs. Im so puzzled and confused I dont know if this is even going to be possible or how to even go about setting it up in a way that it WILL work. If anyone can please help me that would be so wonderful! If you want to see the document I have put together so far I can email it to you. Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
I would need a bit more detail to know how the leave is shown on the s/s.
What is the difference between leave and vacation time? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... Ahh! I got it!!! Now my new task is how to have it calculate their leave and vaction time into their hours.. Any suggestions?! You are a ton of help! Thanks! "Bob Phillips" wrote: num_hours refers to the number of hours worked, 52.5 in your example. Assuming that the hours are in a cell, let's say A1, you just add those formulae to A2 and A3 =MIN(MAX(A1-37.5,0),2.5) =MAX(A1-40,0) When your use puts their time in A1, the others autonmatically calculate HTH MigraineReliever "GettingaHeadache" wrote in message ... I'm not quite sure what to do here, I am new to the advanced formulas in excel. I dont want the employees to have to enter anything but their time. So in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in "num_hours"? Ok lets say that their regular hours total 52.50 which is obviously 15 hrs of overtime but there is going to be 37.5-40 hrs of overtime which is only 2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to where the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs will hit cell "a2" automatically? I am not sure if I am explaining this clear enough but I dont know how else to say it. Please Help!!! "Bob Phillips" wrote: =MIN(MAX(num_hours-37.5,0),2.5) for hours between 37.5 and 40 =MAX(num_hours-40,0) for hours over 40 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... I am trying to make a time sheet for everyone to be able to use in the office. I want it to where when the total in Regular working hours reaches 37.5 it will put the excess working hours into another cell called of course Overtime. My dilema is not only this it is also that there are 2 colums of overtime. There is overtime for 37.5 hrs to 40 and then there is a column for 40+ hrs. Im so puzzled and confused I dont know if this is even going to be possible or how to even go about setting it up in a way that it WILL work. If anyone can please help me that would be so wonderful! If you want to see the document I have put together so far I can email it to you. Thanks! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Sheet
Well there is nothing difficult about what you want, but until you show the
layout there is not much more to say, as it would just be guessing? Mail me the spreadsheet bob (dot) phillips (at) tiscali (dot) co (dot) uk do the obvious with the bits in brackets -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... Leave is when you are sick, have a doctors appointment, sick relative etc. Vacation is when you go on vacation or take a day off to do personal business. Right now I dont have a format for the leave or vacation time my boss just informed me she wants me to work it in there somehow. The setup I have atm is regular hrs, leave hrs, vacation hrs, worked hrs, OT 37.5-40, OT 40+ and total hrs. What I eventually want is for worked hours to take out lunch hour, vacation and leave time. The total hours would include the lunch hour, vacation, and leave time so that way they know what they worked for that whole day. Am i doing double duty on this? I dont want to confuse the payroll with 2 totals plus OT totals let alone confuse the employees... If you want to see the spreadsheet I can email it to you or you can contact me @ . Thanks! "Bob Phillips" wrote: I would need a bit more detail to know how the leave is shown on the s/s. What is the difference between leave and vacation time? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... Ahh! I got it!!! Now my new task is how to have it calculate their leave and vaction time into their hours.. Any suggestions?! You are a ton of help! Thanks! "Bob Phillips" wrote: num_hours refers to the number of hours worked, 52.5 in your example. Assuming that the hours are in a cell, let's say A1, you just add those formulae to A2 and A3 =MIN(MAX(A1-37.5,0),2.5) =MAX(A1-40,0) When your use puts their time in A1, the others autonmatically calculate HTH MigraineReliever "GettingaHeadache" wrote in message ... I'm not quite sure what to do here, I am new to the advanced formulas in excel. I dont want the employees to have to enter anything but their time. So in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in "num_hours"? Ok lets say that their regular hours total 52.50 which is obviously 15 hrs of overtime but there is going to be 37.5-40 hrs of overtime which is only 2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to where the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs will hit cell "a2" automatically? I am not sure if I am explaining this clear enough but I dont know how else to say it. Please Help!!! "Bob Phillips" wrote: =MIN(MAX(num_hours-37.5,0),2.5) for hours between 37.5 and 40 =MAX(num_hours-40,0) for hours over 40 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GettingaHeadache" wrote in message ... I am trying to make a time sheet for everyone to be able to use in the office. I want it to where when the total in Regular working hours reaches 37.5 it will put the excess working hours into another cell called of course Overtime. My dilema is not only this it is also that there are 2 colums of overtime. There is overtime for 37.5 hrs to 40 and then there is a column for 40+ hrs. Im so puzzled and confused I dont know if this is even going to be possible or how to even go about setting it up in a way that it WILL work. If anyone can please help me that would be so wonderful! If you want to see the document I have put together so far I can email it to you. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet drop down lists | Excel Discussion (Misc queries) | |||
Time Sheet | Excel Discussion (Misc queries) | |||
Formula to deduct unpaid breaks in time sheet | Excel Discussion (Misc queries) | |||
time sheet calculations | Excel Worksheet Functions | |||
Excel formula for a time sheet | Excel Worksheet Functions |