Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help, How can I deduct time for a timesheet?
I am currently working on a timesheet and its urgent that I can sort
something out today. we work on a flexi basis this lady has a contracted hours of 14:48 hours a work and she worked 12.21 hours last week but it doesn't seem to like to deduct time I think it should be -2.27 (2 hours 27 minutes) but it just comes up ############# I don't think it likes the minutes????? It works ok if she has worked over her contracted hours. Can anyone help me please. Thanks |
#2
|
|||
|
|||
Jo,
The # signs indicate that the column in which the cell with your result displays is formatted too narrowly to show the whole value. Usually you have a value in the cell immediately to the right which needs to display as well. The solution is simply to resize the column to show your entire result. At that point you may wish to reformat the result such that it use less width. Good luck, Joe "Jo" wrote: I am currently working on a timesheet and its urgent that I can sort something out today. we work on a flexi basis this lady has a contracted hours of 14:48 hours a work and she worked 12.21 hours last week but it doesn't seem to like to deduct time I think it should be -2.27 (2 hours 27 minutes) but it just comes up ############# I don't think it likes the minutes????? It works ok if she has worked over her contracted hours. Can anyone help me please. Thanks |
#3
|
|||
|
|||
Xl doesn't handle negative time. You have to separate overtime and less then
contracted time values into separate columns: overtime: =IF(contractedtime<=workedtime,workedtime-contractedtime,"") less then contractedtime =IF(contractedtimeworkedtime,contractedtime-workedtime,"") Regards, Stefi €žJo€ ezt Ã*rta: I am currently working on a timesheet and its urgent that I can sort something out today. we work on a flexi basis this lady has a contracted hours of 14:48 hours a work and she worked 12.21 hours last week but it doesn't seem to like to deduct time I think it should be -2.27 (2 hours 27 minutes) but it just comes up ############# I don't think it likes the minutes????? It works ok if she has worked over her contracted hours. Can anyone help me please. Thanks |
#4
|
|||
|
|||
Hi Jo,
op1: go to menu ToolsOptions, tab 'Calculation' and checj the '1904 data systm' option. op2: present the value as absolute and have a flag in another cell whether positive or negative [C1]=ABS(A1-B1) [D1]=IF(A1B1,"OVER","UNDER") op3: leave it as is since the value is still there so it can be used but not seen. op4: present it as text: =TEXT(ABS(A1-B1),IF(B1A1,"-","")&"hh:mm") Regards, KL "Jo" wrote in message ... I am currently working on a timesheet and its urgent that I can sort something out today. we work on a flexi basis this lady has a contracted hours of 14:48 hours a work and she worked 12.21 hours last week but it doesn't seem to like to deduct time I think it should be -2.27 (2 hours 27 minutes) but it just comes up ############# I don't think it likes the minutes????? It works ok if she has worked over her contracted hours. Can anyone help me please. Thanks |
#5
|
|||
|
|||
Hi Jo
Excel will not show negative time when using the standard date system. That is why you are getting the '####'s. You could choose ToolsOptionsCalculation and select the 1904 date system, and this will allow negative time, but beware, if you are carrying out other date calculations as this will throw those calculations out. Another alternative is to convert the times to decimal time. Excel stores times as fractions of a day, so you have to multiply each value by 24. =(A1*24)-(B1*24) will show a result of -2.45 hours (note not 2 hours 45 mins, but 2.45 hours). You need to format the cell with the formula as General. Regards Roger Govier Jo wrote: I am currently working on a timesheet and its urgent that I can sort something out today. we work on a flexi basis this lady has a contracted hours of 14:48 hours a work and she worked 12.21 hours last week but it doesn't seem to like to deduct time I think it should be -2.27 (2 hours 27 minutes) but it just comes up ############# I don't think it likes the minutes????? It works ok if she has worked over her contracted hours. Can anyone help me please. Thanks |
#6
|
|||
|
|||
Hi Stefi,
Xl doesn't handle negative time. I guess it does, but won't present it in time format inside a cell. Regards, KL |
#7
|
|||
|
|||
What you say is true in general, but it's not what's going on here. Excel
can't handle negative times. On Mon, 19 Sep 2005 04:35:01 -0700, "jr" wrote: Jo, The # signs indicate that the column in which the cell with your result displays is formatted too narrowly to show the whole value. Usually you have a value in the cell immediately to the right which needs to display as well. The solution is simply to resize the column to show your entire result. At that point you may wish to reformat the result such that it use less width. Good luck, Joe "Jo" wrote: I am currently working on a timesheet and its urgent that I can sort something out today. we work on a flexi basis this lady has a contracted hours of 14:48 hours a work and she worked 12.21 hours last week but it doesn't seem to like to deduct time I think it should be -2.27 (2 hours 27 minutes) but it just comes up ############# I don't think it likes the minutes????? It works ok if she has worked over her contracted hours. Can anyone help me please. Thanks |
#8
|
|||
|
|||
Thanks, KL,
I realized the difference! Regards, Stefi €žKL€ ezt Ã*rta: Hi Stefi, Xl doesn't handle negative time. I guess it does, but won't present it in time format inside a cell. Regards, KL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
conditional formatting with time values | Excel Discussion (Misc queries) | |||
Formula to deduct unpaid breaks in time sheet | Excel Discussion (Misc queries) | |||
unmet challenge | Excel Worksheet Functions | |||
How can Excel do daily timesheet, -lunch time, & not use colon in. | Excel Worksheet Functions |