Difference Between two dates and times

waikikiguy

New Member
Joined
Feb 23, 2010
Messages
7
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!
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Apr 26, 2010
Messages
26
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

Board Regular
Joined
Dec 16, 2002
Messages
65
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

New Member
Joined
May 1, 2014
Messages
39

ADVERTISEMENT

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

ITWare2008

Board Regular
Joined
Apr 16, 2010
Messages
174
@Alanfd

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

Vândalo
 

Alanfd

Board Regular
Joined
Dec 16, 2002
Messages
65
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,154
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top