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

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

I am calculating Time in Column I, it gives me ##### values when Column F is after 12 o clock, why is this happening, may be due to it is coming in negative value, how to Tackle this...

Formula in Cell I11 is: =IFERROR(IF(AND(H10="stopped",H11="Running"),(F11-F10),""),"")

Attached is Excel file and snapshot

I have posted this question here too https://www.facebook.com/groups/3932..._post_approved

2. ## Re: Calculating time value returns hash(#####)

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

3. ## Re: Calculating time value returns hash(#####)

what,s the solution to this, as it should return 02:00

4. ## Re: Calculating time value returns hash(#####)

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.

5. ## Re: Calculating time value returns hash(#####)

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

6. ## Re: Calculating time value returns hash(#####)

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 #####.

7. ## Re: Calculating time value returns hash(#####)

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).

8. ## Re: Calculating time value returns hash(#####)

Then C10 must be 0 and not 12

9. ## Re: Calculating time value returns hash(#####)

Column C, column D and column E data is coming from machine. It can't be changed, in F column i combine these three column values

10. ## Re: Calculating time value returns hash(#####)

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

11. ## Re: Calculating time value returns hash(#####)

If F10 is 12 30pm and F11 2 30pm, we get the same error in our original file

12. ## Re: Calculating time value returns hash(#####)

How did you calculate the 2:30 PM

13. ## Re: Calculating time value returns hash(#####)

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

14. ## Re: Calculating time value returns hash(#####)

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

15. ## Re: Calculating time value returns hash(#####)

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.`

16. ## Re: Calculating time value returns hash(#####)

I have highlighted data which is coming from machine in the attached file

17. ## Re: Calculating time value returns hash(#####)

@ChemistB Now this is what i wanted...Thank you so much....

18. ## Re: Calculating time value returns hash(#####)

Thank you guys for your kind time
@popipipo
@MrShorty
It was good discussion with you people

@ChemistB thank you for fantastic solution...

19. ## Re: Calculating time value returns hash(#####)

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

20. ## Re: Calculating time value returns hash(#####)

@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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1