Calculating time value returns hash(#####)

F11-F10 gives a negative time answer. (2:30-12:30=-10:00)
Excel can't handle this

What is the expected result?

You are correct that it is a negative time (format I11 as general and you will see a negative fraction of a day). Interestingly, I can open your sheet is LO calc (the programmers at The Document Foundation have decided that there is no reason not to allow negative times to display), and it will display 14:00 in I11 -- meaning 14 hours runtime. So it is interpreting the time in F10 and 12:30:30 PM and the time in F11 as 2:30:30 AM for a total of 14 hours. Is that the correct interpretation of the problem?

I might have interpreted as 12:30:30 PM, but that you intended the 2:30:30 in F11 to be 2:30:30 PM (or 14:30:30). If I enter 14 into C11, then I11 returns 2:00 -- 2 hours runtime -- and it is not a negative time.

I don't think we can recommend a good solution without knowing exactly how to interpret the inputs. With that information, we should be able to recommend a proper solution.

Why 02:00?

Or do you mean 12:30am and 2:30pm

In holland we say
12:30 and 14:30 Then you don't have a problem

Let,s make it clear.
In Cell F10 it,s 12:30 am and in Cell F11 it,s 02:30 am. Plant stopped at 12:30am and started at 02:30am, Calculating breakdown time(by subtracting F11 from F10) and it should be 02:00 hours.

Note: As highlighted in picture it,s working fine but below when subtracting small value from large, it,s giving negative time,due to which it,s showing #####.

Let,s make it clear. In Cell F10 it,s 12:30 am and in Cell F11 it,s 02:30 am.

This is probably the source of the confusion, F10 is NOT 12:30 AM. Excel is understanding this to be 12:30 PM (add the AM/PM indicator to the time format in column F to see).

As near as I can tell, Excel's TIME() function treats times on a 24 hour clock basis. I would suggest that, if F10 is supposed to be 12:30 AM, you enter 0 in C10 so that Excel will see this as 0:30:30 or 12:30:30 AM. Entering times on a 24 hour clock should eliminate this issue (until you have a case where the "stopped" time is 11:30 PM and the "running" time is 2:30 AM).

Then C10 must be 0 and not 12

How do you know that 12:30 is 12:30pm or 12:30AM

How did you calculate the 2:30 PM

As i told column C, D & E data is coming from machine...and in F column its is combined.
The purpose is not Am or PM. we need to calculate stoppage time of machine, so when it stops it sends current hour,min, sec to excel and when it starts it sends hour,min,sec to excel. Now we combine it in other coulmn as a date

Which value give the machine in column C if you result is 2:30PM?

Take a look at the file and look how excel count with times

Just to be clear, when 12:30 is entered into Excel, it sees it as 12:30 PM. In order to have this work we're going to have to make this assumption.
Your time differential from time 1 to time 2 will never be over 12 hours.

Formula:

Please Login or Register  to view this content.

Hi,

First of all, 12:30 AM is not a correct time format. It is either 12:30 PM for midday or 00:30 AM for night.
Secondly, I would suggest you to use a different time format such as; 21:30 instead of 09:30 PM (or 08:30 instead of 08:30 AM).
The third thing to consider is to use ABS function to change the negative value to a positive value.
For example : 00:30 - 11:30 equals -10:30, however ABS(00:30-11:30) equals 10:30.
If you apply these conditions to your formula and time table, you will not see ########
Note: you should consider that if stopped time is PM and running time is AM, than for a correct calculation you need to modify your formula. For example from 12:30 PM to 02:30 AM it stopped 14 hours. But if you simply subtract 12:30-02:30 you will find 10 hours differens. In this case to reach correct answer 14 hours, you need to put a extra condition in case the stopped time is an PM and running time is AM. (for example (24 - 12:30)+2:30=14
Regards
Kzm

@kzm
Actually, in the USA, 12:30 AM is considered the norm and Excel defaults to AM/PM over the 24 hour nomenclature. That being said, I concur that the 24 hour nomenclature used in most countries is preferred to AM/PM.