# Calculate the Days, Hours, and Minutes between two dates

#### Tyron

##### Active Member
Hey Everyone,

Hopefully one of you guys can help me. I have been searching the web and it looks like most people are only interested in calculating the hours or the days between two dates. I on the other hand am looking for some excel formulas to help me determine in 3 seperate cells the length of time between two date:tiems.

The catch is that it will be constantly updated since one of those dates is determined by the NOW() function.

Currently this is what I have:

Data entry requires that the user enter the Date in cell A2 and the Time in cell B2. The length of time for the project is C2 which is in the form of hours and minutes (ex 27.88)
 A B C D E F G H I J 1 Date Time Length Minutes Date Time Days Hours Minutes 2 08/26/2014 13:22 27.88 1673 08/27/2014 17:14 0 6 ??? 3 4 Current Date

<tbody>
</tbody>
Code:
``````So as you can see After the values for A, B, and C have been entered
the rest is calcualted:

D2: [COLOR=#0000cd]IF(A2="",0,(60*LEFT(C2, 2))+(0.6*RIGHT(C2, 2)))[/COLOR]
make sure that C2 is formatted for number with two decimal places after
otherwise the formula in D2 will compute the project lenght incorrectly.

E2: [COLOR=#0000cd]INT((((A2+B2)+(D2/24/60))))[/COLOR]
This calculates the estimated finish date after seperating just the date out.

F2: MOD((((A2+B2)+(D2/24/60))),1)
This calculates the estimated finish time after sepertating just the time out

G2: [COLOR=#0000cd](E2+F2)-NOW()[/COLOR]
This combines the values of the estimated completion time and then subtracts
it from the current Date/Time to get the days left

H2:[COLOR=#0000cd] IF((((E2+F2)-\$B\$4)*24)>=24, 23,IF((((E2+F2)-\$B\$4)*24)<=0,0,(((E2+F2)-\$B\$4)*24)))[/COLOR]
This combines the estimated completion date/time and subtracts it from the
current date/time followed by a check of grater then or equal to 24 and then
another check to see if it is less then or equal to zero to remove negative
numbers.  Once these two checks are done it evaluates to either 23 hours or
less then that.  The reason for this is because I want it to indicate anything
over 23 hours as a day+hours.

I2:[COLOR=#ff0000]???[/COLOR]
This has got me stumped.  I am trying to figure out how many minutes are
left for the project, but only be between 0 and 59.``````

The end result should be something like this:
 G H I 1 DAYS HOURS MINUTES 2 0 6 22

<tbody>
</tbody>
Any help is appreciated. I am aware that there is a slight margin for error in the formula for D2 if the Length is a whole number such as =27.00, but at the moment it is not important.

Thanks again.

Ty

Last edited:

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Your margin of error can be formatted as [h]:mm to show greater than 24 hours

any date time combination is purely whole number (DATE), decimals (24 HRS)

Hi,

I can remember a brilliant and simple solution from Barry Houdini, with complete dates in cells A1 and B1

Code:
``=INT(B1-A1)&" days "&TEXT(B1-A1,"h"" hrs ""m"" mins ""s"" secs""")``

HTH

Hey James06,

That might work with the exception that the "Length" is not in the form of conventional hours:minutes. Each hour consits of 100 units. Thus you find 27.88 as a possibility.

Thanks.

later

Ty

Hey James06,

Thanks for the formula. Unfortunately I am having some difficulty.

Code:
``````This is what I am using right now with your code:
I2: [COLOR=#0000ff]=INT((E2+F2)-A4)[/COLOR]          'Shows just the days.  Works fine
J2: [COLOR=#0000ff]=TEXT((E2+F2)-A4,"h")[/COLOR]  'Shows just the hours.  Works fine
K2: [COLOR=#ff0000]=TEXT((E2+F2)-A4,"m")[/COLOR] 'displays the number 1 rather than the just
the actual minutes.
L2: [COLOR=#0000ff]=TEXT((E2+F2)-A4,"s" )[/COLOR]  'Shows just the seconds.  Works fine.

So as I must display the information in multiple cells I have broken the formula:
[COLOR=#0000ff]=INT((E2+F2)-A4)&" days "&TEXT((E2+F2)-A4,"h"" hrs ""m"" mins ""s"" secs""")[/COLOR]``````
Into 4 pieces. I have also put the results into one cell as your formula shows and it works fine. For some reason, maybe I am missing something, when I split the minutes into its cell it only shows the number 1. I have recalculated many minutes by (this is done by just picking an empty cell and hitting the "Delete" Key) and still just the number 1 shows.

Any help would be appreciated. Thank you.

later

Ty

mole999 said:
Your margin of error can be formatted as [h]:mm to show greater than 24 hours

any date time combination is purely whole number (DATE), decimals (24 HRS)
Tyron said:
Hey James06,

That might work with the exception that the "Length" is not in the form of conventional hours:minutes. Each hour consits of 100 units. Thus you find 27.88 as a possibility.

Thanks.

later

Ty​

Hey James06,

Thanks for the formula. Unfortunately I am having some difficulty.

Code:
``````This is what I am using right now with your code:
I2: [COLOR=#0000ff]=INT((E2+F2)-A4)[/COLOR]          'Shows just the days.  Works fine
J2: [COLOR=#0000ff]=TEXT((E2+F2)-A4,"h")[/COLOR]  'Shows just the hours.  Works fine
K2: [COLOR=#ff0000]=TEXT((E2+F2)-A4,"m")[/COLOR] 'displays the number 1 rather than the just
the actual minutes.
L2: [COLOR=#0000ff]=TEXT((E2+F2)-A4,"s" )[/COLOR]  'Shows just the seconds.  Works fine.

So as I must display the information in multiple cells I have broken the formula:
[COLOR=#0000ff]=INT((E2+F2)-A4)&" days "&TEXT((E2+F2)-A4,"h"" hrs ""m"" mins ""s"" secs""")[/COLOR]``````
Into 4 pieces. I have also put the results into one cell as your formula shows and it works fine. For some reason, maybe I am missing something, when I split the minutes into its cell it only shows the number 1. I have recalculated many minutes by (this is done by just picking an empty cell and hitting the "Delete" Key) and still just the number 1 shows.

Any help would be appreciated. Thank you.

later

Ty

Hey guys,

I actually found a solution to the minutes. I still don't know why it is misbehaving though.

What I did to avoid the problem was to wrap the complete formula with the MID() function and I was able to pull out the minutes. It was wierd though in that I needed to indicate to MID to use 3 characters instead of 2 like I expected. here is my formula:

Code:
``[COLOR=#0000ff]=MID(TEXT((E2+F2)-A4,"h"" hrs ""m"" mins ""s"" secs"""),7,3)[/COLOR]``

If you guys figure out what I did wrong previously with just formula in the red text above I would appreciate it if you could explain it to me. Thanks again.
Btw, remember that the text in A4 is supposed to represent the NOW() function.

later

Ty

Replies
3
Views
642
Replies
4
Views
184
Replies
6
Views
490
Replies
6
Views
224
Replies
8
Views
277

1,219,828
Messages
6,150,474
Members
450,966
Latest member
Yali

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