+ Reply to Thread
Results 1 to 24 of 24

Calculation time between start and end time

  1. #1
    Registered User
    Join Date
    05-20-2009
    Location
    Switzerland
    MS-Off Ver
    Excel 365
    Posts
    45

    Calculation time between start and end time

    Hello,

    Hope someone can help me with the following

    In a data file I have Start and End date of production jobs.
    I would like to calculate the "run" time which is the difference between the 2 dates

    Start date End date
    5/27/2020 4:09:28 PM 5/27/2020 4:13:22 PM
    5/27/2020 4:21:57 PM 5/27/2020 4:33:46 PM
    5/27/2020 4:51:13 PM 5/27/2020 4:55:21 PM
    5/27/2020 6:12:03 PM 5/27/2020 6:16:59 PM
    5/27/2020 6:23:41 PM 5/27/2020 6:26:27 PM
    5/27/2020 6:52:25 PM 5/27/2020 6:56:23 PM


    When I do End date - start date I get an error

    Anybody know the correct way to calculate the minutes?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,461

    Re: Calculation time between start and end time

    Are dates real dates or dates stored as text?

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Calculation time between start and end time

    What error?

    Are you still using Excel 2007? If not, please update your profile.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Calculation time between start and end time

    Just a thought: where have these dates come from? They are in US format (mm/dd/yyyy) not standard European format (dd/mm/yyyy) - we need to see this workbook.

  5. #5
    Registered User
    Join Date
    05-20-2009
    Location
    Switzerland
    MS-Off Ver
    Excel 365
    Posts
    45

    Re: Calculation time between start and end time

    I am sorry for the late reply
    I have meanwhile updated my Excel version

    The data is coming from an .csv file which I opened in my Excel

    I have added a sample file to this post
    Attached Files Attached Files

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,573

    Re: Calculation time between start and end time

    Which date format you are using "DD/MM/YYYY" or "MM/DD/YYYY"
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Registered User
    Join Date
    05-20-2009
    Location
    Switzerland
    MS-Off Ver
    Excel 365
    Posts
    45

    Re: Calculation time between start and end time

    Dd/mm/yyyy

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Calculation time between start and end time

    The entries are text, not genuine dates, hence the issue.

  9. #9
    Registered User
    Join Date
    05-20-2009
    Location
    Switzerland
    MS-Off Ver
    Excel 365
    Posts
    45

    Re: Calculation time between start and end time

    is there any way to convert them to time?

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Calculation time between start and end time

    I came up with this monster some time ago:

    =IF(ISTEXT(C3),--SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT(TEXT(C3,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9)&REPLACE(SUBSTITUTE(LEFT(TEXT(C3,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9,""))," ","/")+IF(ISTEXT(C3),--MID(C3,FIND(" ",C3)+1,99),C3),C3)

    You might be able to adapt it.

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,573

    Re: Calculation time between start and end time

    try
    =MOD(MOD(LOOKUP(2,RIGHT(C2,{8,9,10,11,12})+0),1)-MOD(LOOKUP(2,RIGHT(B2,{8,9,10,11,12})+0),1),1)
    or
    =(AGGREGATE(14,6,MID(C2,{3,4},{1;2})+0,1)&"/"&AGGREGATE(14,6,LEFT(C2,{1,2})+0,1)&MID(C2,FIND("/",C2,4),256))+0-(AGGREGATE(14,6,MID(B2,{3,4},{1;2})+0,1)&"/"&AGGREGATE(14,6,LEFT(B2,{1,2})+0,1)&MID(B2,FIND("/",B2,4),256))+0

  12. #12
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,573

    Re: Calculation time between start and end time

    1st Formula will work if the time is between 24 hours

  13. #13
    Registered User
    Join Date
    05-20-2009
    Location
    Switzerland
    MS-Off Ver
    Excel 365
    Posts
    45

    Re: Calculation time between start and end time

    Quote Originally Posted by AliGW View Post
    I came up with this monster some time ago:

    =IF(ISTEXT(C3),--SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT(TEXT(C3,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9)&REPLACE(SUBSTITUTE(LEFT(TEXT(C3,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9,""))," ","/")+IF(ISTEXT(C3),--MID(C3,FIND(" ",C3)+1,99),C3),C3)

    You might be able to adapt it.
    THE MONSTER WORKS!!!

    Thanks

  14. #14
    Registered User
    Join Date
    05-20-2009
    Location
    Switzerland
    MS-Off Ver
    Excel 365
    Posts
    45

    Re: Calculation time between start and end time

    Quote Originally Posted by samba_ravi View Post
    1st Formula will work if the time is between 24 hours
    Thanks for the formula.
    So this also only works if it is in same day.
    The advantage is that I have the direct result
    Last edited by obionenairobi; 05-29-2020 at 10:38 AM.

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,573

    Re: Calculation time between start and end time

    Quote Originally Posted by obionenairobi View Post
    Thanks for the formula.
    So this also only works if it is in same day.
    The advantage is that I have the direct result
    I think this will work for next day also (with in 24 hours range)

  16. #16
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,461

    Re: Calculation time between start and end time

    To convert B2 to real "date:time"
    Please Login or Register  to view this content.
    Then the time difference:
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    05-02-2013
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Calculation time between start and end time

    I'm clueless when it comes to formulae. Can the above example be used in my little time sheet?


    -Criss
    Attached Files Attached Files

  18. #18
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,461

    Re: Calculation time between start and end time

    Quote Originally Posted by sifubear View Post
    I'm clueless when it comes to formulae. Can the above example be used in my little time sheet?
    -Criss
    WHAAAAT?
    Thing seems easy and in standard format?
    We spent lot of time with your info so far.
    Just like this:

    =(C4+D4-A4-B4)*24

  19. #19
    Registered User
    Join Date
    05-02-2013
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Calculation time between start and end time

    Told you I was clueless! LOL

    Your formula worked perfectly as long as start and stop don't cross from one day to the next. I can live with that!

  20. #20
    Registered User
    Join Date
    05-02-2013
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Calculation time between start and end time

    I stand corrected. My dates were wrong. It works perfectly! Thank you!

  21. #21
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,741

    Re: Calculation time between start and end time

    Here is an alternative solution using Power Query

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    1
    Start End Subtraction Hours Minutes Seconds
    2
    5/27/2020 16:09
    5/27/2020 16:13
    0.002708333
    0
    3
    54
    3
    5/27/2020 16:21
    5/27/2020 16:33
    0.008206019
    0
    11
    49
    4
    5/27/2020 16:51
    5/27/2020 16:55
    0.00287037
    0
    4
    8
    5
    5/27/2020 18:12
    5/27/2020 18:16
    0.003425926
    0
    4
    56
    6
    5/27/2020 18:23
    5/27/2020 18:26
    0.001921296
    0
    2
    46
    7
    5/27/2020 18:52
    5/27/2020 18:56
    0.00275463
    0
    3
    58
    Sheet: Sheet4

    and here is the Mcode.

    Please Login or Register  to view this content.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Calculation time between start and end time

    @Sifubear - for future reference

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  23. #23
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,461

    Re: Calculation time between start and end time

    Quote Originally Posted by AliGW View Post
    Administrative Note:
    Please see Forum Rule #4 about hijacking and start a new thread for your query.[/URL]
    I was thinking it is same OP!!!

  24. #24
    Registered User
    Join Date
    05-02-2013
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Calculation time between start and end time

    I am sorry for hijacking this thread. I thank you all for your answers, and I won't hijack again. Thanks all!

+ 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. Start time and End time Calculation sheet and uneditable of Time
    By tgray007 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 03-17-2017, 10:02 AM
  2. Replies: 0
    Last Post: 03-04-2016, 06:38 PM
  3. Replies: 4
    Last Post: 03-02-2016, 02:46 AM
  4. [SOLVED] Basic Calculation of Start - End Time
    By onmyway in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2015, 02:29 PM
  5. Calculation of time difference between the Start date & time & End Date & time
    By Harry Jones in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-17-2012, 03:54 PM
  6. [SOLVED] Calculation of seconds from Start time and End time
    By uday1969 in forum Excel General
    Replies: 12
    Last Post: 06-13-2012, 03:21 PM
  7. Replies: 3
    Last Post: 03-27-2012, 01:07 PM

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