Problem with datedif calculations for dates_time

[rolta100]

the datedif formula is having a problem.

=DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&" months, "&DATEDIF(A1,B1,"md")&" days "&TEXT(MOD(B1-A1,1),"h "" hours "" m"" minutes "" s ""seconds""") 

Format: Date/time format (mm/dd/yyyy hh:ss)

Consider
A1= 10/6/2013 18:00
B1= 10/6/2013 18:00
Result: "0 years 0 months 0 days 0 hours 0 minutes 0 seconds"

Now consider
A1= 10/6/2013 18:00
B1= 10/7/2013 18:00
Result: "0 years 0 months 1 days 0 hours 0 minutes 0 seconds"

Now consider again,
A1= 10/6/2013 18:00
B1= 10/7/2013 17:00
Result: "0 years 0 months 1 days 23 hours 0 minutes 0 seconds"
which is practically wrong. it should be "0 years 0 months 0 days 23 hours 0 minutes 0 seconds"

Now consider
A1= 10/6/2013 18:00
B1= 11/7/2013 17:00
Result: 0 years, 1 months, 1 days 23 hours 0 minutes 0 seconds
which is practically wrong again. it should be "0 years 0 months 30 days 23 hours 0 minutes 0 seconds"

Now we consider
A1= 10/6/2013 18:00
B1= 11/7/2014 17:00
Result: 1 years, 1 months, 1 days 23 hours 0 minutes 0 seconds
which is practically wrong again. it should be "1 years 0 months 30 days 23 hours 0 minutes 0 seconds"

I Changed formula,

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

A1= 10/6/2013 18:00
B1= 10/7/2013 17:00
Result is correct "0 days 23 hrs 0 mins 0 secs"

once again i changed formula

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

the result is 0 yrs 1 mnts 0 days 23 hrs 0 mins 0 secs
once again wrong


How it can be solved? Someone please help me.

[daffodil11]

What standard are you using for the length of a month and year?

Some dates are 30 days apart; in some instance this is a month, in other instances this is less than a month. Some years are even shorter than others.

[rolta100]

I'm using simple Standard the difference between two dates should come in the format "years months days hours minutes seconds".

It is observed that the part with datedif just simple calculating the difference of dates but not linked with time the example is


A1= 10/6/2013 18:00
B1= 10/7/2013 17:00
Result: "0 years 0 months 1 days 23 hours 0 minutes 0 seconds" which is practically/technically wrong. it should be "0 years 0 months 0 days 23 hours 0 minutes 0 seconds"


when i changed formula

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

it is calculted correctly upto days hours minutes seconds. but in this case it gives incorrect answer for year and month, the modified formula is

=INT(B1-A1)/365&" yrs "&TEXT(B1-A1,"m"" mnts ""d"" days ""h"" hrs ""m"" mins ""s"" secs""") 

A1=10/6/2013 18:00
B1=10/7/2014 18:00


Result: 1.0027397260274 yrs 12 mnts 31 days 0 hrs 0 mins 0 secs


once again i changed formula,

=CONCATENATE(TRUNC(ROUND(((B1-A1)*1440),0)/1440,0)," Days
",TRUNC(MOD(ROUND((B1-A1)*1440,0),1440)/60)," Hours
",ROUND(MOD(ROUND((B1-A1)*1440,0),60),0), " Minutes") 

Result:
366 Days
0 Hours
0 Minutes


Once again i get failed to calculate with "years months days hours minutes seconds". I need all parameters to be linked.

Actual formula i'm using as

=IFERROR(IF(B1-A1=0,"",IF(DATEDIF($A1,$B1,"y")=0,"",DATEDIF($A1,$B1,"y")&"  yrs, ")&IF(DATEDIF($A1,$B1,"ym")=0,"",DATEDIF($A1,$B1,"ym")&" mths, ")&IF(DATEDIF($A1,$B1,"md")=0,"",DATEDIF($A1,$B1,"md")&" days, ")&TEXT(MOD($B1-$A1,1),"h "" hrs, "" m "" mins, and "" s ""sec""")),"") 

[Izandol]

DATEDIF will ignore time component (there are no interval codes for time values). You may try:
=DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&" months, "&DATEDIF(A1,B1,"md")-(MOD(A1,1)>MOD(B1,1))&" days "&TEXT(MOD(B1-A1,1),"h "" hours "" m"" minutes "" s ""seconds""")

[rolta100]

thanks it is working, but when i modify my formula for year and month answer is incorrect.

=IFERROR(IF(H7-G7=0,"",IF(DATEDIF($G7,$H7,"y")=0,"",DATEDIF($G7,$H7,"y")-(MOD(G7,1)>MOD(H7,1))&"  yrs, ")&IF(DATEDIF($G7,$H7,"ym")=0,"",DATEDIF($G7,$H7,"ym")-(MOD(G7,1)>MOD(H7,1))&" mths, ")&IF(DATEDIF($G7,$H7,"md")=0,"",DATEDIF($G7,$H7,"md")-(MOD(G7,1)>MOD(H7,1))&" days, ")&TEXT($H7-$G7,"h "" hrs, "" m "" mins, and "" s ""sec""")),"") 

G7= 04/02/2013 5:00:00
H7= 05/03/2014 05:55:00
Result: 1 yrs, 1 mths, 1 days, 0 hrs, 55 mins, and 0 sec

But when i change to
G7= 04/02/2013 5:00:00
H7= 05/03/2014 05:55:00
Result: 0 yrs, 0 mths, 0 days, 23 hrs, 55 mins, and 0 sec
Here the difference for month and year not calculated, change in time is calculating day, as well as it should sequentially calculate for month and year, if year is not completing by a day or a second it should be not show as a year, same goes with a month too. so far we are succeeded upto day as if to complete day a sec is lacking it cannot be said as a complete day it means 0 days 23 minutes 59 secs. Same need to be calculated with month and year.
Parallelly, it should also consider days (31,30,february(28, 29)) in a month and calculate further for year.
here in this case it should come 0 year 12 months 27 days 23 hrs 55 mins and 0 secs

DATEDIF will ignore time component (there are no interval codes for time values). You may try:
=DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&" months, "&DATEDIF(A1,B1,"md")-(MOD(A1,1)>MOD(B1,1))&" days "&TEXT(MOD(B1-A1,1),"h "" hours "" m"" minutes "" s ""seconds""")

[Izandol]

I do not understand why you modify rest of formula? Also your two examples are the same and 0 years 12 months may not be correct.

[rolta100]

we are having two fields of dates with time. we need the calculation accuracy upto 99.99% as we need to present CISO and other higher authorities.If calculation accuracy is reducing to 99% that will do but the incorrect calculation will not work.

the incidents are occured is start date/time and Resolved time is End Date/time.

the result should be accurate. thats why i shared you in earlier post

if to complete a day, a sec is lacking it cannot be said as a complete day it means 0 days 23 minutes 59 secs. Same need to be calculated with month and year.

The same should happen with month and year. I tried to calculate but failed.