Problem with datedif

rolta100

New Member
Joined
Nov 29, 2013
Messages
1
the datedif formula is having a problem.
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""")
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
PHP:
 =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.
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top