Excel showing ##### as result of time calculation...

[SeethaSharma]

Hi,

I have a spreadsheet for Time and Attendance purposes. This spreadsheet has columns like the following:

Leave Taken Hours Worked
Start Stop Total Leave Assigned Hours - Total Leave

Assigned hours are always 8:00. All cells are defined as Time (13:30). Calculations work fine except when Total Leave is also 8:00. Then, the Hours Worked column shows #####. This is not due to a simple cell width issue -- I have tried.

I had a similar problem earlier with another column in the same spreadsheet and I solved it by turning on "Precision as displayed". But that doesn't work in this case.

Out of desperation, I turned on the 1904 date system. That shows - 0:00 in the problem cells!

It looks like the problem is that when Excel is subtracting 8:00 from 8:00, it somehow ends up with - 0:00...

Please help!

Seetha

 

[rvnguy]

SeethaSharma,

You need to use the time function (not the time format) in these cells. Xcel can not calculate with the cell formatted for time display.

Time Function (under Functions)
Returns the serial number of a particular time. The serial number returned by TIME is a decimal fraction ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.).

Syntax=TIME(12, 0, 0)

Hope this helps

rvnguy
"I know everything..I just can't remember it all

 

[xlbo]

The problem will be that although you are showing 08:00, what is actually there is likely to be 08:00:01 or similar. What you are seeing are FORMATTED REPRESENTATIONS of time. Because you are not showing the full time, you cannot be sure of what you are doing with it

Try changing your formats from hh:mm to hh:mm:ss and see what is there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tsh73]

I think the problem is that you need a time as a difference but Excel stores time as actual (valid) dateTime.
So while you can trick Excel into showing you "0:00" by entering a format
h:mm;"0:00"
that would show 0:00 for all (possible?) negatives also

 

[SkipVought]

Hi,

Time values are just NUMBERS as fractions of a day.

.25 is a quarter of a day which is 6 hours or 6:00.

However, if you are doing arithmetic and a result is NEGATIVE, then ######.

Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]

 

[SeethaSharma]

Hi,

Thanks to all of you for your responses. I appreciate the time you have taken to answer my question.

However, I am still running into trouble with this spreadsheet. The cell where I have the issue is a calculated cell. If I use the TIME function, it returns a 0:00 irrespective of the result of the calculation. For example, if the calculation yields - 0:00, TIME shows 0:00 (which is what I want). However, if the calculation yields 8:00, then also TIME shows 0:00!

Any other ideas?!! Is there any way to force Excel to treat all values as positive -- that is, when Excel sees a negative value in a cell, it is displayed as positive (that is, - 0:00 will be displayed as 0:00)? That might solve the problem...

Again, many thanks for the responses.

Seetha

 

[xlbo]

Please try reading the posts

Try formatting the cell as hh:mm:ss and post back to let us know what is there

Alternatively, change the cell format to numeric with 4 decimal places and post that number

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

The problem is the inherent inaccuracies in floating point arithmetic. Depending on your precise start and stop times your calculations can produce a very very small negative number which Excel fails to convert to a display time. Try wrapping your calculation in an ABS function to make the result a very very small positive number instead - Excel should then correctly convert it to 0:00 for display purposes.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SeethaSharma]

Hi Tony and Geoff,

Thank you for taking the time to respond again. Tony's suggestion of using ABS is probably what's going to work.

Geoff -- formatting the cells involved in the calculation as hh:mm:ss showed 8:00:00 and 8:00:00 in both cells but the result cell still shows ###### when formatted hh:mm:ss.

Formatting the cells as number (with 4 decimal places) shows 0.3333 in both cells and the result cell has 0:00:00. Interestingly, when I change the format back to time (hh:mm:ss) from number (with 4 decimals) it shows one time as 7:59:57 and the other as 8:00:00 -- obviously, this is the reason for the negative result of the subtraction.

I am sorry I did not mention this in my earlier post. I forgot about it as it did not help me with a solution.

Thank you once again.

Seetha

 

[xlbo]

Yes - ABS should certainly work. If you are getting a 3 second difference in times, then I miscalculated how many decimal places you should show the number to.
08:00:00 = 0.33333333 or 0.3333 to 4 d.p.
07:59:57 = 0.33329861 or 0.3333 to 4 d.p.

If I had suggested 5 d.p. it would've been plain that you are in fact dealing with 2 (very slightly) different numbers as you would've seen
0.33333
0.33330

 

[TonyJollans]

I suspect the 3-second difference is a result of using "precision as displayed".

If you have it set and format 08:00:00 as a number with 4 decimal places then accuracy is permanently lost. If you then format it back as a time, it is 0.3333(00000...0) which is interpreted as 07:59:57.

There really is no way of avoiding the underlying issue and using ABS is one way round it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SeethaSharma]

Hi Tony and Geoff,

Thanks again. Yes, it seems ABS is the easiest way to solve the issue. I do appreciate both your explanation of the underlying problem.

Have a nice day.

Seetha