Help, How can I deduct time for a timesheet?

[Jo]

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

 

[jr]

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

 

[Stefi]

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:

 

[KL]

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

 

[Roger Govier]

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

 

[KL]

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

 

[Myrna Larson]

What you say is true in general, but it's not what's going on here. Excel
can't handle negative times.

 

[Stefi]

Thanks, KL,
I realized the difference!
Regards,
Stefi

„KL†ezt írta: