Negative time calculations

[Dave East]

I am creating a timesheet requiring negative time to be shown in the Excel
2007 spreadsheet. I have learnt how to calculate the number of hours worked
and show how many hours have been worked over the contracted hours. This
gives a positive value.

The problem appears when the contracted hours exceeds the actual hours.
This would give a negative value but the spreadsheet shows this as a series
of ############### symbols. It will not show -2 hrs 15 min for example.

Is there a way around this problem, please.

 

[Dave Peterson]

If you change to the 1904 date system, then you'll be able to see negative
times.

But any existing date will be off by 4 years and one day.

This can cause a problem with that workbook--and when you copy|paste between
workbooks.


Saved from a previous post:

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates.
Edit|PasteSpecial|Click Add (or Subtract) depending on which workbook you want
to fix.

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

 

[Tyro]

Excel for Windows does not handle negative time calculations. Excel for Mac
does. See Chuck Pearson's website (www.cpearson.com) for solutions

Tyro

 

[Chip Pearson]

See Chuck Pearson's website (www.cpearson.com) for solutions

Who's site?


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tyro]

Sorry, typo

Who's site?


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tyro]

Will not refer to your site again.

Tyro

Who's site?


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ron Rosenfeld]

Excel for Windows does not handle negative time calculations. Excel for Mac
does.

Excel for Windows certainly can handle negative time calculations.

I have this in my Excel 2002 running under Windows XP Pro:

A1: 1/5/2008 6:30 AM (format m/d/yyyy h:mm AM/PM )
A2: 1/4/2008 4:15 PM
A3: =A2-A1 --> -14:15 (format [h]:mm )

--ron

 

[Tyro]

And by the way, correct English grammar is Whose site, not who's site (who
is site).

Tyro

 

[Dave East]

Ron,

Thank you for this but it does not fully answer my problem. Perhaps I
didn't explain myself clearly enough.

I am using Excel 2007 on Vista Home Premium.

The timesheet that I have can calculate the hours worked in a session or
day. Over five days these hours can then be added to give the total worked
for the week.

However there is a requirement for a minimum weekly total of hours worked.
My difficulty lies in that I need to work in time units to get the hours
worked but when I deduct that total from the minimum and expect a minus
result (e.g. 18 hrs 30 min as the minimum requirement but only 10 hrs 45 min
worked should show 9 hrs 45 min owed, i.e. -9 hrs 45 min) the program will
not give me a minus answer in time units. It will if I convert the figures
to integers but I had hoped that i could avoid that step.

How can I use the Mac system in my PC system?

Thank you again. I am looking forward to your reply, hopefully,

David Shaw

Excel for Windows does not handle negative time calculations. Excel for Mac
does.

Excel for Windows certainly can handle negative time calculations.

I have this in my Excel 2002 running under Windows XP Pro:

A1: 1/5/2008 6:30 AM (format m/d/yyyy h:mm AM/PM )
A2: 1/4/2008 4:15 PM
A3: =A2-A1 --> -14:15 (format [h]:mm )

--ron

 

[Ron Rosenfeld]

However there is a requirement for a minimum weekly total of hours worked.
My difficulty lies in that I need to work in time units to get the hours
worked but when I deduct that total from the minimum and expect a minus
result (e.g. 18 hrs 30 min as the minimum requirement but only 10 hrs 45 min
worked should show 9 hrs 45 min owed, i.e. -9 hrs 45 min) the program will
not give me a minus answer in time units. It will if I convert the figures
to integers but I had hoped that i could avoid that step.

There are several possible solutions.

1. As Dave Peterson already mentioned, you can select to use the 1904 date
system: Tools/Options/Calculation/Workbook Options and Select 1904 date
system.

This will have the side affect of changing any dates already entered by adding
4 years (and 1 day) to them. And also, you would have to ensure that other
users opened the workbook with that option enabled.

2. Depending on how you are going to use this information, you could just
ignore the non-display (or use conditional formatting to "white it out"). The
stored value is still correct.

3. If you absolutely need to have it displayed, you could, for example, make
the result positive but use conditional formatting to make it bold/red or
something else that lets you know it is negative. You would then NOT be able
to use that cell result in dependent formulas, but would have to go back to the
source cells.

4. You could use conditional formatting to white out the negative number; then
display the result in an adjacent cell using the ABS function to make it
positive, and also using conditional formatting to make it bold/red. Then you
could use the original cell in dependent formulas.

There are also other possible solutions. Not sure what would be most
appropriate, but I would pick #1 first, if that doesn't mess up other users and
uses.

Failing that, I would use #4 and use conditional formatting to display the
negative result in an adjacent cell. The formatting for the base cell would be
to format the font as the background (e.g. white) if cell value < 0; and the
formatting for the display cell would be to set the font to bold/red if the
parent cell is <0; or to white if the target cell is >=0.
--ron