Difference in hours between two dates

**skifizzo** · 01-20-2020, 11:55 AM

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?

**Pepe Le Mokko** · 01-20-2020, 11:57 AM

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

**Pepe Le Mokko** · 01-20-2020, 11:58 AM

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

**skifizzo** · 01-20-2020, 12:16 PM

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

**Pepe Le Mokko** · 01-20-2020, 12:24 PM

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

**skifizzo** · 01-21-2020, 04:32 AM

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?

**Pepe Le Mokko** · 01-21-2020, 04:41 AM

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) ?

**Pepe Le Mokko** · 01-21-2020, 04:43 AM

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

**skifizzo** · 01-21-2020, 05:02 AM

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

**BMV** · 01-21-2020, 05:10 AM

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

**davsth** · 01-21-2020, 05:24 AM

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))

**skifizzo** · 01-21-2020, 06:15 AM

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

**skifizzo** · 01-21-2020, 06:22 AM

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)

**davsth** · 01-21-2020, 06:36 AM

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

**BMV** · 01-21-2020, 06:36 AM

=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

**davsth** · 01-21-2020, 06:42 AM

=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

**skifizzo** · 01-21-2020, 06:43 AM

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.

**BMV** · 01-21-2020, 06:58 AM

Originally Posted by skifizzo

the result should be around 875 hours because there are 18 days of difference)... any advice on this?

davsth and me wrote about formatting.

**skifizzo** · 01-21-2020, 07:23 AM

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!

Difference in hours between two dates

LinkBack

Thread Tools

Rate This Thread

Display

Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Re: Difference in hours between two dates

Thread Information

Users Browsing this Thread

Similar Threads

Difference between two dates in hours

Difference between 2 dates in HOURS

What is the difference in hours between dates?

Required difference between two dates/times in hours

Need difference between two dates/times in hours

Calculate the difference in hours between two dates?

[SOLVED] How do I calculate difference in days & hours between two dates e.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions