0

I have two columns of dates and times, formatted as Custom dd/mm/yyyy hh:mm.

A third column is =C3-B3, =D3-D3, =x3-x3, etc.

excel cells

As you can see, it does not tell the difference in days, hours and minutes, as I would like, but shows the difference in a date format, with the days, hours and minutes being correct, but with January 1900 added for some weird reason.

How would I get a result with just the difference in days, hours and minutes?

I originally added a link to an example file HERE, but I noticed that when I open the link in Incognito Mode, to make sure that it was Public, I noticed that it turns the dates into mm/dd/yyyy format, which is really stupid and even more confusing.

Improve this question

2 Answers 2

Reset to default
-2

enter image description here Write this formula in cell D3 and fill down as necessary.

=INT(C3-B3)&" Days "&HOUR(MOD(C3-B3,1))&" Hour "&MINUTE(MOD(C3-B3,1))&" Minutes"

I hope this helps.

Improve this answer
9
  • 1
    Thanks for your help, but the other answer gave exactly the same results with a little modification and as it is much more simple for me to understand.
    – paradroid
    Commented Feb 18, 2018 at 7:18
  • Actually, your results of 31 Days and 61 Days were not correct, but I don't understand the formula to say why.
    – paradroid
    Commented Feb 18, 2018 at 7:23
  • 31 Days is the difference between 01/02 and 01/01 also 61 Days is between 01/08 and 01/06 ,,,, you can count the difference even on fingers.
    – Rajesh Sinha
    Commented Feb 18, 2018 at 7:48
  • 1
    The first four rows use my original UK date format (dd/mm/yyyy), but the next six rows use the US (mm/dd/yyyy) format and the last two use the UK format again, but with a different separator. Making these sorts of mistakes results in a big mess in your calculations!
    – paradroid
    Commented Feb 18, 2018 at 7:53
  • @paradroid,, Check the Screen Shot, I've applied UK Format to both Date Columns as you did and for your knowledge Date Format doesn't effects the Calculation, the Formula I've suggested is Technically a correct method to find the difference in Days ,, Hours and Minutes.
    – Rajesh Sinha
    Commented Feb 18, 2018 at 8:08
1

Change the cell format in Column C to custom.

In the custom format box type: d:hh:mm

This should produce the desired result.

EDIT:

Would ya look at that? Excel has a 'DATEDIF' function. Syntax:

=DATEDIF(Date1, Date2, Interval)

According to Chip Pearson, this was documented once, in Excel 2000, but has been in Excel since '95 and is treated like other Formulae's drunk cousin (ie Excel knows it lives a happy and useful life, but will not speak of it in polite conversation.)

There are some examples on the link above showing how to list differing date components in the result. It doesn't appear to deal with time, however.

Improve this answer
5
  • Thanks, that was an easier solution to what I was expecting. I used d "days," h "hrs" mm "mins" to get the result I wanted.
    – paradroid
    Commented Feb 18, 2018 at 7:18
  • If you apply the Custom Format as you suggested d:hh:mm, Date & Time Value in Column C of C3 will looks like 20:12:06 and after you Subtract C3 to B3 and apply the same Format d:hh:mm you get 1:16:38. This you mean as 1day 16Hrs 38Minutes,, which actually looks like a Time 1:16:38.
    – Rajesh Sinha
    Commented Feb 18, 2018 at 8:00
  • @RajeshS I thought the same, but it gave the correct results and I modified it to be more readable, as my previous comment explains.
    – paradroid
    Commented Feb 18, 2018 at 8:05
  • This only works if the number of days does not exceed 31, after that it will restart at 1. the d does not return the number of days but the day of the month.
    – Scott Craner
    Commented Feb 18, 2018 at 14:55
  • DATEDIF formula finds the difference in Year, Month and Days but the OP demands to find difference in days, hours and minutes!!
    – Rajesh Sinha
    Commented Feb 19, 2018 at 7:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .