How to calculate date and time difference with odd months?

[jeff07kp]

Hi to all, ok I basically have 5 columns
D E F G H

Collumns D and F are Start and Finish dates written as (MM/dd/year), and E and G Start and finish times. While H is the result table in which I express the difference in fix hour times

I need to calculate the difference of these periods but in hours


=IF(MONTH(H15)=MONTH(F15),((DAY(H15)-DAY(F15))*24)+(HOUR(I15)+MINUTE(I15)/60)-(HOUR(G15)+MINUTE(G15)/60),(((DAY(H15)+31)-DAY(F15))*24)+(HOUR(I15)+MINUTE(I15)/60)-(HOUR(G15)+MINUTE(G15)/60))



The formula above works, but as I put a dates between months it provides me a negative hour.... how can I fix this.
<table x:str="" style="border-collapse: collapse; width: 212px; height: 62px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="4" width="64"> <col style="width: 47pt;" width="63"> <col style="width: 47pt;" width="62"> <tbody><tr style="height: 33.75pt;" height="45"> <td class="xl28" style="border-left: medium none; width: 48pt;" x:str="'Start Cargo Ops Time" width="64">
</td> <td class="xl28" style="border-left: medium none; width: 48pt;" width="64">
</td> <td class="xl28" style="border-left: medium none; width: 47pt;" x:str="'Finish Cargo Ops Time" width="63">
</td> <td class="xl28" style="border-left: medium none; width: 47pt;" width="62">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669">
</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="39583">
</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.91666666666666663">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(MONTH(D2)=MONTH(B2),((DAY(D2)-DAY(B2))*24)+(HOUR(E2)+MINUTE(E2)/60)-(HOUR(C2)+MINUTE(C2)/60),(((DAY(D2)+31)-DAY(B2))*24)+(HOUR(E2)+MINUTE(E2)/60)-(HOUR(C2)+MINUTE(C2)/60))">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="0.29583333333333334">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;" x:num="39598">
</td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="8.7499999999999994E-2">
</td> <td class="xl33" style="border-left: medium none;" x:num="" x:fmla="=IF(MONTH(D4)=MONTH(B4),((DAY(D4)-DAY(B4))*24)+(HOUR(E4)+MINUTE(E4)/60)-(HOUR(C4)+MINUTE(C4)/60),(((DAY(D4)+31)-DAY(B4))*24)+(HOUR(E4)+MINUTE(E4)/60)-(HOUR(C4)+MINUTE(C4)/60))">
</td> </tr> </tbody></table>

 

[xld]

=((f13+g13)-(d13+e13))*24

 

[DonkeyOte]

Not sure I follow, something like this ?

Excel Workbook
	D	E	F	G	H
2	15/07/2008	08:00	10/08/2008	17:30	633:30
3	10/08/2008	19:30	10/08/2008	23:30	4:00
Sheet5

Note: H is Custom Format of [h]:mm

XLD's formula will give you decimal of 633.5 and would not require Custom Format (just General)

 

[Jonmo1]

How about

=TEXT((F2+G2)-(D2+E2),"[h]")*1

 

[jeff07kp]

Ok, basically what Laws 10 said. But how can excel automatically give me this rate in decimal with out using format??

 

[DonkeyOte]

Xld's formula (number * 24) will give you answer in decimal without need for custom format.

 

[Jonmo1]

But how can excel automatically give me this rate in decimal with out using format??

By using either of the other 2 suggestions..

 

[DonkeyOte]

just for clarification -- the reason we all did the same thing...

Dates in XL are whole numbers -- Jan 1 1900 is 1, Jan 2 1900 is 2 and so on
(unless using 1904 as base which is non-standard option),

Times in XL are decimal

So by adding the end time & end date together you get 1 number, adding the start date & time you get another number -- the difference between the two numbers can then in turn be translated into a date/time format by Excel given that whole number equates to days and remainder equates to time.

HTH

 

[jeff07kp]

Thanks guys.. that was beutifull

My formula was way more complicated and this just simples everything up