Date and time subtraction

[aldea187]

Hello Everyone,

I have what i thought being a simple subtraction giving me some issues. I have two date ranges i want to subtract. They include d/m/yyyy hh:mm. I have tried a few formatting and formulas, but do not get the right answer. See below for date examples

Created	Resolved	Difference
10/26/2011 22:02 <tbody> </tbody>	8/15/2014 15:41 <tbody> </tbody>
11/8/2011 15:32 <tbody> </tbody>	10/29/2014 19:30 <tbody> </tbody>
11/15/2011 14:37 <tbody> </tbody>	2/6/2014 8:53 <tbody> </tbody>
11/17/2011 13:51 <tbody> </tbody>	10/29/2014 19:30 <tbody> </tbody>

<tbody>
</tbody>

I would like to output in ddd:hh:mm but it does carry over months or years.

Thanks,

 

[shg]

d (or dd) means day of the month to Excel, so it won't go over 31 days.

Row\Col	A​	B​	C​	D​
1​			days​	h:m​
2​	10/26/2011 22:02​	08/15/2014 15:41​	1023​	17:39​
3​	11/08/2011 15:32​	10/29/2014 19:30​	1086​	3:58​
4​	11/15/2011 14:37​	02/06/2014 08:53​	813​	18:16​
5​	11/17/2011 13:51​	10/29/2014 19:30​	1077​	5:39​
6​
7​			C2: =INT(B2-A2)	D2: =MOD(B2-A2,1)

 

[MarcelBeug]

The format you suggest is only possible for date/time stamps, not for elapsed times.
(ddd would give you the day abbreviation, not the day number).

My suggestion is the following formula in C2 (assuming data starts at A2)

=INT(B2-A2)&"d "&TEXT(B2-A2,"hh:mm")

 

[JST013]

So your end result is days and hours:mins?

Familiar with vba?

If so, how about a custom function? This will work if you data is a little more spread out...A few things need to be added if your cells contain both the date and the times to be subtracted.

Function subtractdates(a As Date, b As Date, c As Variant, d As Variant)
    a = CDate(a)
    b = CDate(b)
    
    
    thedays = b - a
    thehours = d - c
    thehours = Format(thehours, "Short Time")
    subtractdates = thedays & " " & thehours
End Function

 

[aldea187]

Thanks Guys. Forgot to post this. Kinda put it aside and just now picked back up.


@shg
This works well, but separates days and hour:min and i see why now. I am trying to get it to be joined into a field.


@MarcelBeug
I like this alot but I am having issues averaging. With the way the int and mod are, i have been unable to average it. I like how it is displayed the best because it is in a singlecell, but unless i can get the math to work ill have to use the other option


@JST013
I am not that strong with VB, but this looks like a good idea. Thanks,