+ Reply to Thread
Results 1 to 20 of 20

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

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    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
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

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

    F11-F10 gives a negative time answer. (2:30-12:30=-10:00)
    Excel can't handle this
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

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

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

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,646

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

    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. #6
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    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 #####.
    Attached Images Attached Images
    Last edited by Zahid0111; 05-05-2020 at 11:19 AM.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,646

    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. #8
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

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

    Then C10 must be 0 and not 12

  9. #9
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    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. #10
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

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

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

  11. #11
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    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. #12
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

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

    How did you calculate the 2:30 PM

  13. #13
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    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
    Last edited by Zahid0111; 05-05-2020 at 11:48 AM.

  14. #14
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

    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
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    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: copy to clipboard
    Please Login or Register  to view this content.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  16. #16
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

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

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

  17. #17
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

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

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

  18. #18
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    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. #19
    Registered User
    Join Date
    04-17-2020
    Location
    Turkey
    MS-Off Ver
    2016
    Posts
    1

    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
    Last edited by kzmsimsek; 05-05-2020 at 01:37 PM.

  20. #20
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating Investment Returns Based On Time and Differing Interest Rates
    By EdwardSnowden in forum Excel General
    Replies: 11
    Last Post: 05-07-2019, 02:39 PM
  2. Help calculating stock investment returns vs S&P 500
    By mattmcg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2013, 02:12 PM
  3. Calculating cumulative returns
    By TrueTears in forum Excel General
    Replies: 0
    Last Post: 03-05-2013, 09:47 AM
  4. calculating annualised returns + Standard deviation of returns
    By Jaspal in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-01-2013, 09:22 AM
  5. [SOLVED] Calculating Monthly Returns
    By duration in forum Excel General
    Replies: 17
    Last Post: 09-18-2012, 07:33 AM
  6. Calculating Average Annual Returns from a Series of Annual Returns
    By Bruinsfan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-03-2012, 09:50 PM
  7. Calculating Year to Date returns
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2010, 08:00 AM

Tags for this Thread

Bookmarks

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