Spreadsheet Timecard
 

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

The problem I am having is that when I enter a negative value (in
format -0:02) I get a popup error stating "the formula you typed
contains an error. If you are not typing a formula avoid typing an = or
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 previous
month's +/- (in this case -) ignores the negative value. If it's a
positive value, it works fine.

Any suggestions?


--
B. Baumgartner
------------------------------------------------------------------------
B. Baumgartner's Profile: http://www.excelforum.com/member.php...o&userid=23107
View this thread: http://www.excelforum.com/showthread...hreadid=383682

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

If not, please share your formulas so we can better understand what it
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

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=383682

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:46
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 so
desire (I don't use macros or VB)


--
B. Baumgartner
------------------------------------------------------------------------
B. Baumgartner's Profile: http://www.excelforum.com/member.php...o&userid=23107
View this thread: http://www.excelforum.com/showthread...hreadid=383682

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=383682