Help determining elapsed time between two dates and times in excel 2010 using military time

[icepole4]

[TABLE="width: 442"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]Entrance Date [/TD]
[TD]Entrance Time[/TD]
[TD]Depart Date[/TD]
[TD]Depart Time[/TD]
[TD]elapsed time[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2013[/TD]
[TD="align: right"]22:52[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]01:35[/TD]
[TD="align: right"]-21:15[/TD]
[/TR]
[TR]
[TD="align: right"]12/4/2013[/TD]
[TD="align: right"]11:16[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD="align: right"]16:55[/TD]
[TD="align: right"]05:40[/TD]
[/TR]
[TR]
[TD="align: right"]12/4/2013[/TD]
[TD="align: right"]13:59[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD="align: right"]14:47[/TD]
[TD="align: right"]00:89[/TD]
[/TR]
[TR]
[TD="align: right"]8/12/2013[/TD]
[TD="align: right"]19:24[/TD]
[TD="align: right"]8/13/2013[/TD]
[TD="align: right"]20:03[/TD]
[TD="align: right"]00:80[/TD]
[/TR]
[TR]
[TD="align: right"]9/15/2013[/TD]
[TD="align: right"]03:45[/TD]
[TD="align: right"]9/16/2013[/TD]
[TD="align: right"]04:00[/TD]
[TD="align: right"]00:56[/TD]
[/TR]
[TR]
[TD="align: right"]9/18/2013[/TD]
[TD="align: right"]18:51[/TD]
[TD="align: right"]9/19/2013[/TD]
[TD="align: right"]18:57[/TD]
[TD="align: right"]00:07[/TD]
[/TR]
[TR]
[TD="align: right"]2/26/2013[/TD]
[TD="align: right"]14:34[/TD]
[TD="align: right"]2/27/2013[/TD]
[TD="align: right"]14:29[/TD]
[TD="align: right"]-00:04[/TD]
[/TR]
[TR]
[TD="align: right"]6/19/2013[/TD]
[TD="align: right"]16:12[/TD]
[TD="align: right"]6/20/2013[/TD]
[TD="align: right"]14:41[/TD]
[TD="align: right"]-01:70[/TD]
[/TR]
[TR]
[TD="align: right"]12/4/2013[/TD]
[TD="align: right"]19:05[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]-02:04
[/TD]
[/TR]
</tbody>[/TABLE]


In the table above I am trying to calculate the elapsed time between entrance and departure in hours and minutes. To calculate I entered the following formula in the elapsed time column

=((C2+D2)-(A2+B2))

Seems like a rock solid formula; however as you can see from my elapsed time outcomes; it's not coming out correctly. The formula is seemingly not even taking the date into account even though its being added to the time.

The formatting of the Date fields is, m/dd/yyyy
The formatting of the time fieds is, 00":"00
Formatting of the elapsed time field is, 00":"00

Is the military time throwing excel off...is my formatting wrong? Any help would be appreciated.

 

[Weazel]

try something like

E2=(C2+D2)-(A2+B2) formatted as [h]:mm

 

[icepole4]

try something like

E2=(C2+D2)-(A2+B2) formatted as [h]:mm

Just tried it...instead of a number I now get ########################

Thanks for the help though, hopefully someone out there can help because I am stumped.

 

[Weazel]

I'm not sure but it seems to be working ok for me

E2=(C2+D2)-(A2+B2) formatted as [h]:mm


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Entrance Date[/TD]
[TD]Entrance Time[/TD]
[TD]Depart Date[/TD]
[TD]Depart Time[/TD]
[TD]elapsed time[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

12/31/2013​

[/TD]
[TD]

22:52​

[/TD]
[TD]

1/2/2014​

[/TD]
[TD]

1:35​

[/TD]
[TD]

26:43​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

12/4/2013​

[/TD]
[TD]

11:16​

[/TD]
[TD]

12/5/2013​

[/TD]
[TD]

16:55​

[/TD]
[TD]

29:39​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

12/4/2013​

[/TD]
[TD]

13:59​

[/TD]
[TD]

12/5/2013​

[/TD]
[TD]

14:47​

[/TD]
[TD]

24:48​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

8/12/2013​

[/TD]
[TD]

19:24​

[/TD]
[TD]

8/13/2013​

[/TD]
[TD]

20:03​

[/TD]
[TD]

24:39​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

9/15/2013​

[/TD]
[TD]

3:45​

[/TD]
[TD]

9/16/2013​

[/TD]
[TD]

4:00​

[/TD]
[TD]

24:15​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

9/18/2013​

[/TD]
[TD]

18:51​

[/TD]
[TD]

9/19/2013​

[/TD]
[TD]

18:57​

[/TD]
[TD]

24:06​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

2/26/2013​

[/TD]
[TD]

14:34​

[/TD]
[TD]

2/27/2013​

[/TD]
[TD]

14:29​

[/TD]
[TD]

23:55​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

6/19/2013​

[/TD]
[TD]

16:12​

[/TD]
[TD]

6/20/2013​

[/TD]
[TD]

14:41​

[/TD]
[TD]

22:29​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

12/4/2013​

[/TD]
[TD]

19:05​

[/TD]
[TD]

12/5/2013​

[/TD]
[TD]

17:00​

[/TD]
[TD]

21:55​

[/TD]
[/TR]
</tbody>[/TABLE]