the datedif formula is having a problem.
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,
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
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.
PHP:
=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,
PHP:
=INT(B1-A1)&" days "&TEXT(B1-A1,"h"" hrs ""m"" mins ""s"" secs""")
B1= 10/7/2013 17:00
Result is correct "0 days 23 hrs 0 mins 0 secs"
once again i changed formula
PHP:
=INT(B1-A1)&" yrs "&TEXT(B1-A1,"m"" mnts ""d"" days ""h"" hrs ""m"" mins ""s"" secs""")
once again wrong
How it can be solved? Someone please help me.
Last edited: