Calculate the Days, Hours, and Minutes between two dates

[Tyron]

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>

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

 

[mole999]

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)

 

[James006]

Hi,

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

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

HTH

 

[RatExcel]

Your sheet: https://app.box.com/s/rpmtpy5sbfliyeso0cdr

 

[Tyron]

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

 

[Tyron]

Hey James06,

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

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

 

[Tyron]

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)

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​

​

Sorry about that James06. I meant to address this as Mole999.

 

[Tyron]

Hey James06,

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

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,

Thanks for all your help.

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:

[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