+ 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 2016
    Posts
    9,409

    Re: Calculation time between start and end time

    Are dates real dates or dates stored as text?
    Quang PT

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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 2021
    Posts
    8,904

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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 2021
    Posts
    8,904

    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 2021
    Posts
    8,904

    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 2021
    Posts
    8,904

    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 2016
    Posts
    9,409

    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
    12

    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 2016
    Posts
    9,409

    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
    12

    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
    12

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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 2016
    Posts
    9,409

    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
    12

    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