+ Reply to Thread
Results 1 to 19 of 19

Difference in hours between two dates

  1. #1
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Difference in hours between two dates

    Hi everyone,

    I need to calculate the diffrence in hours between two dates.

    The dates look like this:

    11/30/2019 09:06 AM
    12/11/2019 08:57 PM

    I know that I have to subtract the two dates to get the result but the problem is that Excel does not recognize their format. I tried to customize the format but without success.
    The only way I was able to convert this text into a date is with Text to Columns, but I loose the information about the hour so the subtraction between the two dates does not give the hours difference.

    Could someone give me an advice?
    Attached Files Attached Files
    Last edited by skifizzo; 01-20-2020 at 12:14 PM.

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Difference in hours between two dates

    Administrative note

    Welcome to the forum

    missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Difference in hours between two dates

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  4. #4
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Difference in hours between two dates

    Hi Pepe, thanks for the head-up!
    I've uploaded the sheet, I'm using Excel 2013.

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Difference in hours between two dates

    And if you just select the column - Text to columns and then click "Finish" without going through ANY other steps?
    And custom format the result as [h]:mm to be on the safe side
    Last edited by Pepe Le Mokko; 01-20-2020 at 12:26 PM.

  6. #6
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Difference in hours between two dates

    Hi Pepe,
    thanks for your suggestion, I tried it but doesn't work.
    I selected the colunmn, went to Text to Columns, left Delimited as data type and clicked "Finish", but everything remains the same.
    Do you have any other suggestion?

  7. #7
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Difference in hours between two dates

    Works great for me
    Try using =DATVEALUE(A2)+TIMEVALUE(A2)
    in another column and pull down (same for col B)
    What regional settings are you using (from your PC) ?

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Difference in hours between two dates

    Eventually
    enter 1 in a cell somewhere and copy it
    Select the dates in col A then - Paste special - Multiply
    If it works the dates will shift to the right . Remove any manual alignment first

  9. #9
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Difference in hours between two dates

    Still nothing... I work from Poland, I'm attaching a screenshot of my regional settings, maybe that's the problem
    Attached Images Attached Images

  10. #10
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    948

    Re: Difference in hours between two dates

    try
    =REPLACE(B2;1;6;MID(B2;4;3)&LEFT(B2;3))-REPLACE(A2;1;6;MID(A2;4;3)&LEFT(A2;3))

  11. #11
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,063

    Re: Difference in hours between two dates

    this is a bit painful, but hopefully works, you may need to adjust , of ; to work for you

    =DATE(MID(B2,7,4),LEFT(B2,2),MID(B2,4,2))+TIMEVALUE(MID(B2,11,8))-DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))+TIMEVALUE(MID(A2,11,8))

  12. #12
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Difference in hours between two dates

    Hi,
    thanks for your advice. I got a #VALUE! error with this formula
    Attached Images Attached Images

  13. #13
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Difference in hours between two dates

    Hi,
    many thanks for writing this complex formua in order to help me, I really appreciate it.
    In this case I get results in the format I would like, but the results are not correct (all the dates have a difference of at least 48 hrs)
    Attached Images Attached Images

  14. #14
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,063

    Re: Difference in hours between two dates

    Have you tried formatting the results as [hh]:mm so the full days become hours?

  15. #15
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    948

    Re: Difference in hours between two dates

    =DATE(MID(B2;7;4);LEFT(B2;2);MID(B2;4;2))+RIGHT(B2;8)-DATE(MID(A2;7;4);LEFT(A2;2);MID(A2;4;2))-RIGHT(A2;8)
    and
    [н]:mm custom format

    If you need hours as number the
    =(DATE(MID(B2;7;4);LEFT(B2;2);MID(B2;4;2))+RIGHT(B2;8)-DATE(MID(A2;7;4);LEFT(A2;2);MID(A2;4;2))-RIGHT(A2;8))*24
    Attached Files Attached Files
    Last edited by BMV; 01-21-2020 at 06:54 AM.

  16. #16
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,063

    Re: Difference in hours between two dates

    =DATE(MID(B2,7,4),LEFT(B2,2),MID(B2,4,2))+TIMEVALUE(MID(B2,11,8))-DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))-TIMEVALUE(MID(A2,11,8))


    Note the sign change in the formula hopefully with formatting as [hh]:mm that will sort it

  17. #17
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Difference in hours between two dates

    I modified my regional settings to English (United States) and followed again Pepe's advice to just use the Text To Column option without going through any other steps, and finally my data was formatted as Date!
    One last problem is that in the difference between the two dates gives me as a result the difference between the hours without taking into consideration how many days of fifference... In cell C2 for example the result should be around 875 hours because there are 18 days of difference)... any advice on this? thanks everyone for your support.
    Attached Files Attached Files

  18. #18
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    948

    Re: Difference in hours between two dates

    Quote Originally Posted by skifizzo View Post
    the result should be around 875 hours because there are 18 days of difference)... any advice on this?
    davsth and me wrote about formatting.

  19. #19
    Registered User
    Join Date
    01-20-2020
    Location
    Wroclaw
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Difference in hours between two dates

    I was writing the message when you replied, now everything works fine!
    Thank you so much, I'm really impressed of how you all have tried to help me, you're fantastic!
    Cheers!

+ 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. Difference between two dates in hours
    By izzy_992 in forum Excel General
    Replies: 2
    Last Post: 04-06-2017, 01:46 AM
  2. Difference between 2 dates in HOURS
    By itshere in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2015, 02:54 PM
  3. What is the difference in hours between dates?
    By StuartMansfield in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2013, 05:50 AM
  4. Required difference between two dates/times in hours
    By kasi.maddula in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-26-2013, 07:14 AM
  5. Need difference between two dates/times in hours
    By ramsdesk in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-25-2013, 01:25 PM
  6. Calculate the difference in hours between two dates?
    By Jonblomberg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2011, 09:42 AM
  7. [SOLVED] How do I calculate difference in days & hours between two dates e.
    By probi2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2005, 12:06 PM

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