Difference Between two dates and times

[waikikiguy]

Aloha,

I have A1= Date A, B1 = Time A, C1 = Date B, D1 = Time B, E1 = Hours between Date&Time B and Date&Time A

how can I enter a formula that takes the date and times from separate columns and calculates them?

Mahalo in advance!

 

[barry houdini]

You want a formula for E1?

You can just subtract like this

=D1+C1-B1-A1

format as number to get the decimal number of days, e.g. 3 days 12 hours will display as 3.5. If you want to get "3 days 12:00" try this formula

=INT(D1+C1-B1-A1)&" days "&TEXT(D1+C1-B1-A1,"h:mm")

 

[Rajesh D]

Also if you want the Hrs, Minutes & Secs in seperate columns, Pls. try the below which I tried for you

<table x:str="" style="border-collapse: collapse; width: 501pt;" border="0" cellpadding="0" cellspacing="0" width="666"><col style="width: 97pt;" width="129"> <col style="width: 103pt;" width="137"> <col style="width: 96pt;" width="128"> <col style="width: 101pt;" width="134"> <col style="width: 104pt;" width="138"> <tbody><tr style="height: 18.75pt;" height="25"> <td class="xl23" style="height: 18.75pt; width: 97pt;" width="129" height="25">Start Time</td> <td class="xl23" style="border-left: medium none; width: 103pt;" width="137">End Time</td> <td class="xl22" style="border-left: medium none; width: 96pt;" width="128">Result in Hrs</td> <td class="xl22" style="border-left: medium none; width: 101pt;" width="134">Result in Minutes</td> <td class="xl22" style="border-left: medium none; width: 104pt;" width="138">Result in Seconds</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; padding-bottom: 10px; padding-top: 10px;" x:num="39242.440972222219" height="17">6/9/2007 10:35</td> <td class="xl26" x:num="39243.645833333336" style="padding-bottom: 10px; padding-top: 10px;">6/10/2007 15:30</td> <td class="xl25" style="border-top: medium none;" x:num="" x:fmla="=INT((B2-A2)*24)">28</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=MINUTE(B2-A2)">55</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=SECOND(B2-A2)">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl27" x:str="'=INT((D4-C4)*24)">=INT((D4-C4)*24)</td> <td class="xl28" x:str="'=MINUTE(D4-C4)">=MINUTE(D4-C4)</td> <td class="xl28" x:str="'=SECOND(D4-C4)">=SECOND(D4-C4)</td> </tr> </tbody></table>

 

[Alanfd]

This is very helpful, but can I ask you to go a step further and ask how do I see the difference between these two dates and times and get the answer as 28:55 in one cell?

 

[G_Sereez]

=int((d4-c4)*24)&":"&minute(d4-c4)

 

[ITWare2008]

@Alanfd

Or you can format E1 with a custom format [h]:mm:ss

Vândalo

 

[Alanfd]

This is what I exact needed, thanks you so much, I really appreciated the help you have given me on this. Have a great weekend!