Subtracting Dates/Times

[frith]

Hi again all,

I have 5 columns:


A1=start date

B1=start time

C1=end date

D1=end time

E1=the difference in hours/minutes


Now, I need to calculate, in hours/minutes ("32:06", etc.), the time that has elapsed between A1/B1 & C1/D1 --- i.e., if A1 = "11/08/11" and B1 = "18:30" and C1 = "11/11/11" and D1 = "18:35", then I need "72:05" to appear in E1 (also need intra-day as well as inter-day times).


Thanx a lot,
Frith

PS - The spreadsheet times are displayed in military format ("22:35"), if that matters at all.

Cross-Post: http://www.mrexcel.com/forum/showthr...70#post2927470

[Pauleyb]

=INT((C1+D1)-(A1+B1))*24&":"&TEXT((((C1+D1)-(A1+B1))-INT((C1+D1)-(A1+B1)))*24*60,"00")

Pauley

[frith]

=INT((C1+D1)-(A1+B1))*24&":"&TEXT((((C1+D1)-(A1+B1))-INT((C1+D1)-(A1+B1)))*24*60,"00")

Pauley

Thanks, but do I need to "format" anything in E1, as with my existing data all I get there is "-980544:9"

Frith

[ChemistB]

If your values are not formatted as text, I would think you are looking for
(C1+D1)-(A1+B1) and formatted as Custom [h]:mm
Does that work for you?

[Pauleyb]

Formatting is involved. ChemistB's solution is much cleaner. I saw the h:mm format did not work, but did not get to the [h]:mm format.

Attached is a worksheet to show proof of concept. Format your sheet to match.

Pauley

[frith]

Formatting is involved. ChemistB's solution is much cleaner. I saw the h:mm format did not work, but did not get to the [h]:mm format.

Attached is a worksheet to show proof of concept. Format your sheet to match.

Pauley

Thanx guys... Turned out my existing VBA was screwing things up!