# Difference Between two dates and times

#### waikikiguy

##### New Member
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?

Last edited:

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### barry houdini

##### MrExcel MVP
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
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
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
=int((d4-c4)*24)&":"&minute(d4-c4)

#### ITWare2008

##### Board Regular
@Alanfd

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

Vândalo

#### Alanfd

##### Board Regular
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!

Replies
4
Views
188
Replies
3
Views
72
Replies
5
Views
123
Replies
2
Views
151
Replies
2
Views
223

1,181,893
Messages
5,932,671
Members
436,850
Latest member
Jasperlee93

### 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.

### Which adblocker are you using?

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

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