+ Reply to Thread
Results 1 to 18 of 18

time calculation formula not working well

  1. #1
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    time calculation formula not working well

    Dear all,
    I am trying to calculating time period between two dates but some cells have different format which are causing error,
    Please help to solve this,
    sample data is attached.
    imran
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: time calculation formula not working well

    Not sure why you are using TEXT() for this, you could just use...
    =C2-A2
    then format as TIME

    By using TEXT, even if it worked for all those rows, you would have a text answer, and even if you wanted to, you would not be able to sum them
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Dear ford,
    It's ok but what about the errors in result how to solve this,
    Because I have a large data how to convert this to required format,
    Regards,
    imran.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Dear all,
    If someone have different formula to solve this please tell me,
    regards,
    imran.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: time calculation formula not working well

    If you use the formula as I suggested, there are no errors

  6. #6
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Please check the attached file error is available,
    regards,

  7. #7
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: time calculation formula not working well

    Not all in column A is real date.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: time calculation formula not working well

    I used the formula in column E and see no errors?
    A
    B
    C
    D
    E
    1
    OPEN DATE
    RECEIPT DATE
    CLOSE DATE Time period Use this
    2
    12/7/2016 9:37
    12/7/2016 9:39
    12/7/2016 9:39
    0:02
    0:02
    3
    12/7/2016 9:33
    12/7/2016 9:36
    12/7/2016 9:37
    0:03
    0:03
    4
    12/7/2016 7:51
    12/7/2016 7:52
    12/7/2016 7:53
    0:01
    0:01
    5
    7/13/2016 9:48:47
    7/13/2016 9:49:27 AM
    7/13/2016 9:49:40
    #VALUE!
    0:00
    6
    12/7/2016 9:54
    12/7/2016 10:08
    12/7/2016 10:09
    0:14
    0:14
    7
    7/13/2016 11:08:27 AM
    7/13/2016 11:15:03 AM
    7/13/2016 11:15:37 AM
    #VALUE!
    0:07
    8
    7/13/2016 11:41:45 AM
    7/13/2016 11:45:33 AM
    7/13/2016 11:46:10 AM
    #VALUE!
    0:04
    9
    7/13/2016 11:32:25 AM
    7/13/2016 11:35:54 AM
    7/13/2016 11:36:21 AM
    #VALUE!
    0:03



    If you format E5 differently, you will see that it actually contains 0:00:53, but with that formatting, it just shows 0
    Last edited by FDibbins; 07-17-2016 at 11:55 PM.

  9. #9
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Thanks for your support ford,
    But I am sorry to say that here in my computer error is still available, may be the problem is in excel,
    Any how thanks once again for you nice coperation,
    Imran

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: time calculation formula not working well

    Can you show me what your formula is, and maybe upload a sample workbook showing the error?

  11. #11
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Please check the attached image file,
    Regards,
    Attached Images Attached Images

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: time calculation formula not working well

    Sorry, can you upload a sample workbook, it's hard to tell from a pic what you have

  13. #13
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Please check the attached worksheet on which formula is showing error,
    regards,
    Attached Files Attached Files

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

    Re: time calculation formula not working well

    Rows 2:5 are numbers
    Rows 6:8 is text
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: time calculation formula not working well

    popipipo is correct about the text entries, use this instead...
    =IF(ISNUMBER(A2),C2-A2,(DATEVALUE(C2)+TIMEVALUE(C2))-(DATEVALUE(A2)+TIMEVALUE(A2)))

  16. #16
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Dear ford,
    I am sorry to say same problem exist,
    Regards,
    imran.
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: time calculation formula not working well

    Excel has difficulties with importing times from other sources.
    07-12-2016 09:37:36
    7/13/2016 11:08:27 AM
    I think you mean 12 july and 13 july
    Excel see the first as 12 december and translate the / as -

    Import the original file as a textfile then you can say this column is a date column with the correct size

  18. #18
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: time calculation formula not working well

    Thanks Williem,
    I will extract it again, I know this problem rise due to import data from other source,,
    Thanks once again,
    imran.

+ 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. Replies: 1
    Last Post: 04-18-2016, 07:43 AM
  2. Working days calculation with Time stamp
    By karthik premkumar in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-19-2015, 05:22 AM
  3. [SOLVED] Time entry on UserForm displaying inccorectly and calculation not working on the time.
    By Colin Smit in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-12-2014, 01:21 PM
  4. [SOLVED] Time calculation not working past midnight
    By Sophie.Durrant in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2014, 08:18 PM
  5. Replies: 6
    Last Post: 05-20-2013, 07:45 PM
  6. Working on Date /time calculation
    By birims in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2012, 03:20 AM
  7. On open set calculation to manual not working all the time
    By matt260384 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-03-2009, 12:47 AM

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