timesheet problems!
 

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

timesheet problems!
 

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

Dave
--
Brevity is the soul of wit.


"msiz" wrote:

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

timesheet problems!
 

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

"Dave F" wrote:

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

Dave
--
Brevity is the soul of wit.


"msiz" wrote:

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

timesheet problems!
 

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" wrote:

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

"Dave F" wrote:

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

Dave
--
Brevity is the soul of wit.


"msiz" wrote:

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

timesheet problems!
 

Hi

You cannot have negative time, unless you are using the 1904 date system
ToolsOptionsCalculation1904

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" wrote in message
...
hi Dave
using this formula: ie. =F5-$G$1
formatted as [h]:mm
msiz

"Dave F" wrote:

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

Dave
--
Brevity is the soul of wit.


"msiz" wrote:

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

timesheet problems!
 

thank you so much - this worked!
msiz x

"Roger Govier" wrote:

Hi

You cannot have negative time, unless you are using the 1904 date system
ToolsOptionsCalculation1904

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" wrote in message
...
hi Dave
using this formula: ie. =F5-$G$1
formatted as [h]:mm
msiz

"Dave F" wrote:

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

Dave
--
Brevity is the soul of wit.


"msiz" wrote:

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