timesheet problems!

[Guest]

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 (",)

 

[Guest]

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

Dave

 

[Guest]

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

 

[Guest]

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.

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

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

Dave

 

[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

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

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

Dave

 

[Guest]

thank you so much - this worked!
msiz x

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

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

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 (",)