Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Sheet and Comp Time
I am trying to put together a "time sheet" worksheet.
Column A is start time formatted in hh:mm Column B is end time formatted in hh:mm Column C has a formula Bx-Cx and is formatted as hh:mm Now for the problem: If the person works exactly 4 hours I want to make "comp" time for that day 0 If the person works more than 4 hours I want to calculate the "comp" time as hours_worked-4 If the person works less than 4 hours I want to calculate the negative "comp" time as 4-hours_worked I'm running into a problem with the calculations for example: Column A1 = 8:00 AM Column B1 = 2:00 PM Column C1 = 6:00 (Formula is B1-A1) Column D1 = C1-4 I "expect" D1 to show 4 instead it shows 3.78. Column D is formatted as a number with two decimal places. If I try any of the time formats I get an invalid cell (all # signs) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Sheet and Comp Time
Chuck,
Excel stores times as fractions of a day. The last formula should be- =C1-4/24 The result is 02:00. Please post again to say how you went. It's good to know whether this suggested solution worked or required more thought. Regards, JohnI "Chuck Duchon" wrote in message ... I am trying to put together a "time sheet" worksheet. Column A is start time formatted in hh:mm Column B is end time formatted in hh:mm Column C has a formula Bx-Cx and is formatted as hh:mm Now for the problem: If the person works exactly 4 hours I want to make "comp" time for that day 0 If the person works more than 4 hours I want to calculate the "comp" time as hours_worked-4 If the person works less than 4 hours I want to calculate the negative "comp" time as 4-hours_worked I'm running into a problem with the calculations for example: Column A1 = 8:00 AM Column B1 = 2:00 PM Column C1 = 6:00 (Formula is B1-A1) Column D1 = C1-4 I "expect" D1 to show 4 instead it shows 3.78. Column D is formatted as a number with two decimal places. If I try any of the time formats I get an invalid cell (all # signs) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Sheet and Comp Time
Johnl,
Your solution seems to worked. I just need to take the time to build the "IF" logic to handle the exceptions. Thanks for the prompt response! Chuck "JohnI in Brisbane" wrote in message ... Chuck, Excel stores times as fractions of a day. The last formula should be- =C1-4/24 The result is 02:00. Please post again to say how you went. It's good to know whether this suggested solution worked or required more thought. Regards, JohnI "Chuck Duchon" wrote in message ... I am trying to put together a "time sheet" worksheet. Column A is start time formatted in hh:mm Column B is end time formatted in hh:mm Column C has a formula Bx-Cx and is formatted as hh:mm Now for the problem: If the person works exactly 4 hours I want to make "comp" time for that day 0 If the person works more than 4 hours I want to calculate the "comp" time as hours_worked-4 If the person works less than 4 hours I want to calculate the negative "comp" time as 4-hours_worked I'm running into a problem with the calculations for example: Column A1 = 8:00 AM Column B1 = 2:00 PM Column C1 = 6:00 (Formula is B1-A1) Column D1 = C1-4 I "expect" D1 to show 4 instead it shows 3.78. Column D is formatted as a number with two decimal places. If I try any of the time formats I get an invalid cell (all # signs) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Sheet and Comp Time
Johnl was on the right track with the 24 hour factor (1hr = 1/24hrs per
day). Took a several revisions, but his should work. ------------------------ =(B1*24)-(A1*24)-4 ------------------------ Good Luck Tumbleweed "Chuck Duchon" wrote in message ... Johnl, I spoke too soon! Your solution works for POSITIVE numbers but it won't handle negatives. For example A1=8:00 AM B1 = 2:00 PM C1 = 6:00 (Formatted as hh:mm with formula if +B1-A1) D1 = 2:00 (Formatted as hh:mm with formula of C1-4/24) However, if the calculation in D1 results in a negative number I get 00:00 A1=8:00 AM B1 = 11:00 AM C1 = 3:00 (Formatted as hh:mm with formula if +B1-A1) D1 = 0:00 (Formatted as hh:mm with formula of C1-4/24) I need a way to calculate the fact that the person worked 1 hour less than the planned 4 hours. I'm suspecting this is happening due to the fact that Excel sees the value in C1 as a TIME i.e. 3:00 AM versus a value of 3 hours and 0 minutes (which is what I want...) Is there a way to calculate negative hours. Thanks again for your help. Chuck "Chuck Duchon" wrote in message ... Johnl, Your solution seems to worked. I just need to take the time to build the "IF" logic to handle the exceptions. Thanks for the prompt response! Chuck "JohnI in Brisbane" wrote in message ... Chuck, Excel stores times as fractions of a day. The last formula should be- =C1-4/24 The result is 02:00. Please post again to say how you went. It's good to know whether this suggested solution worked or required more thought. Regards, JohnI "Chuck Duchon" wrote in message ... I am trying to put together a "time sheet" worksheet. Column A is start time formatted in hh:mm Column B is end time formatted in hh:mm Column C has a formula Bx-Cx and is formatted as hh:mm Now for the problem: If the person works exactly 4 hours I want to make "comp" time for that day 0 If the person works more than 4 hours I want to calculate the "comp" time as hours_worked-4 If the person works less than 4 hours I want to calculate the negative "comp" time as 4-hours_worked I'm running into a problem with the calculations for example: Column A1 = 8:00 AM Column B1 = 2:00 PM Column C1 = 6:00 (Formula is B1-A1) Column D1 = C1-4 I "expect" D1 to show 4 instead it shows 3.78. Column D is formatted as a number with two decimal places. If I try any of the time formats I get an invalid cell (all # signs) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Sheet and Comp Time
That worked... after dividing the cell containing the time by 24 the result
was a cell that contained the number of hours (i.e. 4:00 AM now looked like 4.00) which is exactly what I wanted. Thanks! Chuck "Tumbleweed" wrote in message ... Johnl was on the right track with the 24 hour factor (1hr = 1/24hrs per day). Took a several revisions, but his should work. ------------------------ =(B1*24)-(A1*24)-4 ------------------------ Good Luck Tumbleweed "Chuck Duchon" wrote in message ... Johnl, I spoke too soon! Your solution works for POSITIVE numbers but it won't handle negatives. For example A1=8:00 AM B1 = 2:00 PM C1 = 6:00 (Formatted as hh:mm with formula if +B1-A1) D1 = 2:00 (Formatted as hh:mm with formula of C1-4/24) However, if the calculation in D1 results in a negative number I get 00:00 A1=8:00 AM B1 = 11:00 AM C1 = 3:00 (Formatted as hh:mm with formula if +B1-A1) D1 = 0:00 (Formatted as hh:mm with formula of C1-4/24) I need a way to calculate the fact that the person worked 1 hour less than the planned 4 hours. I'm suspecting this is happening due to the fact that Excel sees the value in C1 as a TIME i.e. 3:00 AM versus a value of 3 hours and 0 minutes (which is what I want...) Is there a way to calculate negative hours. Thanks again for your help. Chuck "Chuck Duchon" wrote in message ... Johnl, Your solution seems to worked. I just need to take the time to build the "IF" logic to handle the exceptions. Thanks for the prompt response! Chuck "JohnI in Brisbane" wrote in message ... Chuck, Excel stores times as fractions of a day. The last formula should be- =C1-4/24 The result is 02:00. Please post again to say how you went. It's good to know whether this suggested solution worked or required more thought. Regards, JohnI "Chuck Duchon" wrote in message ... I am trying to put together a "time sheet" worksheet. Column A is start time formatted in hh:mm Column B is end time formatted in hh:mm Column C has a formula Bx-Cx and is formatted as hh:mm Now for the problem: If the person works exactly 4 hours I want to make "comp" time for that day 0 If the person works more than 4 hours I want to calculate the "comp" time as hours_worked-4 If the person works less than 4 hours I want to calculate the negative "comp" time as 4-hours_worked I'm running into a problem with the calculations for example: Column A1 = 8:00 AM Column B1 = 2:00 PM Column C1 = 6:00 (Formula is B1-A1) Column D1 = C1-4 I "expect" D1 to show 4 instead it shows 3.78. Column D is formatted as a number with two decimal places. If I try any of the time formats I get an invalid cell (all # signs) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Sheet and Comp Time
then you could just do
=B1-A1-TimeValue("04:00") -- Regards, Tom Ogilvy Chuck Duchon wrote in message ... That worked... after dividing the cell containing the time by 24 the result was a cell that contained the number of hours (i.e. 4:00 AM now looked like 4.00) which is exactly what I wanted. Thanks! Chuck "Tumbleweed" wrote in message ... Johnl was on the right track with the 24 hour factor (1hr = 1/24hrs per day). Took a several revisions, but his should work. ------------------------ =(B1*24)-(A1*24)-4 ------------------------ Good Luck Tumbleweed "Chuck Duchon" wrote in message ... Johnl, I spoke too soon! Your solution works for POSITIVE numbers but it won't handle negatives. For example A1=8:00 AM B1 = 2:00 PM C1 = 6:00 (Formatted as hh:mm with formula if +B1-A1) D1 = 2:00 (Formatted as hh:mm with formula of C1-4/24) However, if the calculation in D1 results in a negative number I get 00:00 A1=8:00 AM B1 = 11:00 AM C1 = 3:00 (Formatted as hh:mm with formula if +B1-A1) D1 = 0:00 (Formatted as hh:mm with formula of C1-4/24) I need a way to calculate the fact that the person worked 1 hour less than the planned 4 hours. I'm suspecting this is happening due to the fact that Excel sees the value in C1 as a TIME i.e. 3:00 AM versus a value of 3 hours and 0 minutes (which is what I want...) Is there a way to calculate negative hours. Thanks again for your help. Chuck "Chuck Duchon" wrote in message ... Johnl, Your solution seems to worked. I just need to take the time to build the "IF" logic to handle the exceptions. Thanks for the prompt response! Chuck "JohnI in Brisbane" wrote in message ... Chuck, Excel stores times as fractions of a day. The last formula should be- =C1-4/24 The result is 02:00. Please post again to say how you went. It's good to know whether this suggested solution worked or required more thought. Regards, JohnI "Chuck Duchon" wrote in message ... I am trying to put together a "time sheet" worksheet. Column A is start time formatted in hh:mm Column B is end time formatted in hh:mm Column C has a formula Bx-Cx and is formatted as hh:mm Now for the problem: If the person works exactly 4 hours I want to make "comp" time for that day 0 If the person works more than 4 hours I want to calculate the "comp" time as hours_worked-4 If the person works less than 4 hours I want to calculate the negative "comp" time as 4-hours_worked I'm running into a problem with the calculations for example: Column A1 = 8:00 AM Column B1 = 2:00 PM Column C1 = 6:00 (Formula is B1-A1) Column D1 = C1-4 I "expect" D1 to show 4 instead it shows 3.78. Column D is formatted as a number with two decimal places. If I try any of the time formats I get an invalid cell (all # signs) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet - Calculating Time Differences for Totals | Excel Discussion (Misc queries) | |||
time sheet determination of what day and time rate | Excel Worksheet Functions | |||
Detailed Time Sheet (overtime, comp time, vacation used) | New Users to Excel | |||
How do I time Hours & mins in excel - Time sheet | Excel Discussion (Misc queries) | |||
I need a time sheet template that verifies time entered against sy | Excel Discussion (Misc queries) |