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

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

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(contractedtime>workedtime,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

Hi Jo,

op1: go to menu Tools>Options, 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(A1>B1,"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(B1>A1,"-","")&"hh:mm")

Regards,
KL


"Jo" <[email protected]> wrote in message
news:[email protected]...
>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

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 Tools>Options>Calculation 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

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

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" <[email protected]> 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

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
>
>
>