Spreadsheet Timecard

[B. Baumgartner]

I have a spreadsheet that calculates the Difference between Time In an
Time out and takes this value to calculate the time you should cloc
out so that you are 0 minutes over or under (+/-). There is a cel
that allows you to enter any time you were over under from the previou
month (in the event that you don't clock out on the time recommended b
the spreadsheet). The Cell that totals the sum of the differences i
time out - time in takes into consideration this manually inputted tim
from the previous month.

The problem I am having is that when I enter a negative value (i
format -0:02) I get a popup error stating "the formula you type
contains an error. If you are not typing a formula avoid typing an = o
a - or precede it with a '"

If I preceed the negative with a ' at the suggestion of the popup box
the cell that calculates the sum of the differences + the previou
month's +/- (in this case -) ignores the negative value. If it's
positive value, it works fine.

Any suggestions

 

[swatsp0p]

It appears to me that with the '-' sign, Excel is treating 00:02 as
text entry. What format are you using to enter positive values?
I am assuming your calculation is based strictly on minutes, you ma
simply need to enter a negative 2 minutes as "-2" (no quotes).

If not, please share your formulas so we can better understand what i
is doing (there are SO many ways to calculate times)

Good Luck

note: this formula will subtract 2 minutes from a given time:

=-(2/24)/60 or =-2/1440

Bruc

 

[B. Baumgartner]

CELL D9 =SUM(D8,($F$16:F$46)) format h:mm
CELL D8 (User Input) (this is where I'm entering the -:02 format h:mm
CELLS F16:F46 = Difference in time of Column D - Column C (16:4
respectively) format general (I use a hidden column to convert the ###
to hh:mm)

You can see it at www.thebaumgartners.com/Boyd/vtc17.xls if you s
desire (I don't use macros or VB

 

[swatsp0p]

Excel does not recognize negative time entries. My suggestion is to
enter the number of minutes as a General format number, then use
another cell to calculate the minute value.

Try this, in your cell D8, formatted General, enter '-2' (no quotes)
and in another cell use the formula "=D8/1440". The result will be
-0.00143. You can then use this in your time calculation. By 'adding'
a negative number, Excel will return the desired results.

Good Luck.

Bruce