Calculating total Years, Months, Days, Hours and Minutes between two dates

[tarheel4ever]

hello,

I am using excel to create a spreadsheet to calculate total time between the two dates. One date is Actual date and time, the second is the System's date and time, and the third is the incident date and time. If system time is less than Actual date i subtract to find total time to find the correct time for the incident date and time.

=IF(Actual_Time>System_Time,INT(Actual_Time-System_Time) & " days, " & HOUR(Actual_Time-System_Time) & " hours, " & MINUTE(Actual_Time-System_Time) & " minutes ",INT(System_Time-Actual_Time) & " days, " & HOUR(System_Time-Actual_Time) & " hours, " & MINUTE(System_Time-Actual_Time) & " minutes ")

This formula is to find total time difference (D4). Format custom: yy" years" m" months" d" days" m" hours"h" minutes"

=IF(System_Time<Actual_Time,Actual_Time-System_Time,"") (A8) Format custom: yy "years", m" months", dd" days", hh "hours", mm" minutes"
This is used to reference the Incident time .


=IF(System_Time<Actual_Time,Actual_Time-System_Time,"") (D12) Format: mm/dd/yyy HH:MM


A8 is still showing 1 month in the calculations what am i doing wrong? I have file to upload if needed

 

[thisoldman]

I can see nothing wrong with your formula. It worked for all the values I supplied. Perhaps there is something amiss with the date+time values used for System_Time and Actual_Time?

Here is another formula that should work This formula eliminates the IF() and some of the repetition of your formula.

=INT(ABS(Actual_Time - System_Time)) & " days, "
 & HOUR(ABS(Actual_Time - System_Time)) & " hours, "
 & MINUTE(ABS(Actual_Time - System_Time)) & " minutes"

 

[tarheel4ever]

Thank you for your reply,

What iam trying to get is the difference from the system time to the actual time. somtimes we run into system that are behind or ahead, I want the difference so we can narrow down the incident time. if we get an offset time we can iput into the DVR we can get the time the incident happened.

I inputted the formula you provided, how would i get the years and months displayed?

 

[tarheel4ever]

My display are as follows:
D3 System_Time format M/D/yyyy hh:mm ;
G3 Actual_Time M/D/yyyy hh:mm;
D4 difference from the two times;
A8 shows if System_Time is behind Actual_time displays here yy"years", m "months etc...;
F8 if Actual_Time is more than System_Time display here yy "years", m "months" etc..;
D10 is Incident time M/D/yyyy hh:mm
D12 if system time behind actual D10-A8 M/D/yyyy hh:mm
G12 if actual time behind system D10+F8 M/D/yyyy hh:mm

In A8 if the times are less than a day the month still shows one month, I'm still confused by this display.

Thanks.

 

[tarheel4ever]

G3 =NOW() formula

 

[thisoldman]

I think these formulas, give what you want. Cells with formulas are yellow filled.

DATEDIF is a Lotus 1-2-3 function that Microsoft doesn't document within Excel. You may find the U.S. version of the Help Documentation at https://support.office.com/en-us/article/DATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c.

The results in A8 and F8 may sometimes appear off by one minute due to the way Excel handles time values internally.

Excel 2012
	A	B	C	D	E	F	G
2				System_Time			Actual_Time
3				04/06/2016 12:24			04/06/2016 13:39
4				0.052031713
5
6
7	System_Time is SLOW by					System_Time is FAST by
8	0 years, 0 months, 0 days, 1 hours, 14 minutes
9				Incident_Time
10				04/01/2016 23:59
11				Slow Correction			Fast Correction
12				04/02/2016 01:13
Sheet1

Cell Formulas
Range	Formula
G3	=NOW()
G12	=IF(D3 <= G3, "", D10 + D4)
D4	=G3-D3
D12	=IF(D3 >= G3, "", D10 + D4)
A8	=IF(D3 >= G3, "", DATEDIF(D3,G3,"Y") & " years, " & DATEDIF(D3,G3,"YM") & " months, " & DATEDIF(D3,G3,"YD") & " days, " & CHAR(10) & HOUR(ABS(D3 - G3)) & " hours, " & MINUTE(ABS(D3 - G3)) & " minutes")
F8	=IF(D3 <= G3, "", DATEDIF(G3,D3,"Y") & " years, " & DATEDIF(G3,D3,"YM") & " months, " & DATEDIF(G3,D3,"YD") & " days, " & CHAR(10) & HOUR(ABS(D3 - G3)) & " hours, " & MINUTE(ABS(D3 - G3)) & " minutes")

 

[tarheel4ever]

Thank You for replying, That solved my A8 problem,

Now D12 would have to be slower than system time in order to capture the incident time according to system time, which would be offset from actual time. Which would be 04/01/2016 22:45 1 hour 45 minutes slow, diff between D3, G3. =IF(D3<G3, D10-A8, "").

CHAR(10) only displays one number why is that? when I removed it it seemed fine.

 

[thisoldman]

Not sure what you were sayingin your last post. The forum software some times garbles formulas with the character ' < ' in them.

The CHAR(10) is not needed for the formulas. I put it in to force the line wrap in the cell to occur between days and hours, CHAR(10) is a way of putting a line break into text in a formula.

 

[tarheel4ever]

I appreciate your help in this matter. I apologize for the confusion. I did delete to two unwanted characters and also made changes. I used your A8 formula to show detectives how far along the time stamp show from every system that we encounter. I changed the "MD" for days to show the passing month completed calendar days.

I had to put this in A8:

"=IF(D3<G3,D3-G3,"")" this is recgonized by the computer to calculate time difference.<g3,d3-g3,"") this="" integer="" is="" recoginze="" by="" the="" computer="" and="" used="" to="" calculate="" time="" difference.

In D12 "=IF(D3<G3,D10-A8,"")"<g3,d10-a8,"") to="" show="" dvr="" offset.

Then I would do the same if the system time was ahead.

Thisoldman I applaud you. Thank you very much.</g3,d10-a8,"")></g3,d3-g3,"")>

 

[tarheel4ever]

A8 If system < actual, Actual - System, ""

this is needed for the computer to calculate time difference

D12 if System < Actual, D10-A8, ""
This shows offeest to input into the DVR so we can get the accurate time the incident happened according to system time stamp which is offest from actual time.


This old man I appreciate all the help you have given. I applaud you! Thanks