timesheet problems!

M

msiz

hi
I have managed to set up a timesheet (all by myself woohoo!!). Having a few
problems though!
I have a rolling timesheet over a 4-5 week period. It adds up the daily
total no problem, but for the daily difference, if it is a negative then it
shows up as ######.
anyone any ideas?
thanks
msiz (",)
 
D

Dave F

What formula are you using that returns ##### for negative values? How is
the cell formatted which returns #####?

Dave
 
D

Dave F

Well, time can't be formatted as negative, so that's why you're getting the
error value. Time begins at point 0, not at negative infinity, so Excel can
only return an error.

One way to work around this is to convert the calculation into numerical form.

Example:
A1= 2:00 AM
B1= 1:00 AM
C1 = B1-A1 --> returns #### if formatted as time. Changing this calculation
to =(B1-A1)*24 converts the time into digit form and returns -1.

Dave
--
Brevity is the soul of wit.


msiz said:
hi Dave
using this formula: ie. =F5-$G$1
formatted as [h]:mm
msiz

Dave F said:
What formula are you using that returns ##### for negative values? How is
the cell formatted which returns #####?

Dave
Click to expand...
Click to expand...
 
R

Roger Govier

Hi

You cannot have negative time, unless you are using the 1904 date system
Tools>Options>Calculation>1904

If your negative time is caused by times running over a 24 hour period,
then try
=MOD(F5-$G$1,1)

--
Regards

Roger Govier


msiz said:
hi Dave
using this formula: ie. =F5-$G$1
formatted as [h]:mm
msiz

Dave F said:
What formula are you using that returns ##### for negative values?
How is
the cell formatted which returns #####?

Dave
Click to expand...
Click to expand...
 
M

msiz

thank you so much - this worked!
msiz x

Roger Govier said:
Hi

You cannot have negative time, unless you are using the 1904 date system
Tools>Options>Calculation>1904

If your negative time is caused by times running over a 24 hour period,
then try
=MOD(F5-$G$1,1)

--
Regards

Roger Govier


msiz said:
hi Dave
using this formula: ie. =F5-$G$1
formatted as [h]:mm
msiz

Dave F said:
What formula are you using that returns ##### for negative values?
How is
the cell formatted which returns #####?

Dave
--
Brevity is the soul of wit.


:

hi
I have managed to set up a timesheet (all by myself woohoo!!).
Having a few
problems though!
I have a rolling timesheet over a 4-5 week period. It adds up the
daily
total no problem, but for the daily difference, if it is a negative
then it
shows up as ######.
anyone any ideas?
thanks
msiz (",)
Click to expand...
Click to expand...
Click to expand...
 
Top