Date Time formula assistance needed

[Dennisr53]

Hi Everyone,

I am in need of assistance I have a spreadsheet that is in Military time and need to calculate the difference between two cells. See example below:

A B C D E
1 Enttered Date Entered Time Collected Date Collected Time subtract collected date and time from entered date and time
2 7/10/2015 0 7/9/2015 2359 formula is ? To get the difference in H:MM format
3 7/27/2015 1540 7/27/2015 1500

4 7/6/2015 2356 7/8/2015 2333 sometimes this is a negative H:MM but I get ########
this is military time this is military time

0 is actually 00:00 or midnight


Any help is greatly appreciated.

Thanks,
Dennis

 

[Comfy]

You need to add the time to the dates and then do the subtraction:

Excel 2010

	A	B	C	D	E	F

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]10/07/2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]09/07/2015[/TD]
[TD="align: right"]2359[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:01[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]27/07/2015[/TD]
[TD="align: right"]1540[/TD]
[TD="align: right"]27/07/2015[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:40[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=($A$2+(INT(B2/100)*60+((B2/100-INT(B2/100))*100))/24/60)-($C$2+(INT(D2/100)*60+((D2/100-INT(D2/100))*100))/24/60)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Blue = Date
Red = Hours converted into minutes
Orange = Minutes
Green = Time Conversion.

This could probably be simplified.

 

[kvsrinivasamurthy]

In E2 then drag down

=A2+INT(B2/100)/24+MOD(B2,100)/(24*60)-C2-INT(D2/100)/24-MOD(D2,100)/(24*60)

 

[Dennisr53]

Comfy, Thank you for the quick response. I have tested the formula and it works great except is does not give me a negative ( - 0:40 ) time value as in my row 2 and row 3 example. The entered date and time is after the collected date and time so it should be a negative hours and minutes.

Any suggestions how to display the negative time>

Thanks again,
Dennis

 

[Dennisr53]

kvsrinivasamurthy Thanks for the assist. With your formula I have the same issue as my response to Comfy's formula. It will not display a negative time difference in H:MM format as is in row 2 and 3 of my example. This is a big dilemma for me as I am doing stats on the results.

Thanks again,
Dennis

 

[kvsrinivasamurthy]

Only display is required or is it used for further calculation.Because negative time can only be displayed can not be used for calculation.

 

[Dennisr53]

Only display is required or is it used for further calculation.Because negative time can only be displayed can not be used for calculation.

I was hoping to use it in calculation of average time but display would be fine. I can come up with another way to include it in avg time.

 

[kvsrinivasamurthy]

Use Two columns E & F
In E2 use the same formula and format the column for Number. Time will be displayed in number (positive and negative) . this can be used for all calculation.

=A2+INT(B2/100)/24+MOD(B2,100)/(24*60)-C2-INT(D2/100)/24-MOD(D2,100)/(24*60)

In F2 Use the formula. Time will be displayed in text format.

=IF(E2<0,"-","")&INT(ABS(E2)*24)&":"&TEXT(INT(MOD(ABS(E2)*24,1)*60),"00")

Both formulas can be dragged down.

 

[Dennisr53]

Use Two columns E & F
In E2 use the same formula and format the column for Number. Time will be displayed in number (positive and negative) . this can be used for all calculation.

=A2+INT(B2/100)/24+MOD(B2,100)/(24*60)-C2-INT(D2/100)/24-MOD(D2,100)/(24*60)

In F2 Use the formula. Time will be displayed in text format.

=IF(E2<0,"-","")&INT(ABS(E2)*24)&":"&TEXT(INT(MOD(ABS(E2)*24,1)*60),"00")

Both formulas can be dragged down.

Thank you. This will work for me. I appreciate the help.