Good day All,

I have a spreadsheet with 6 spin buttons - one for year - two for month - three for day - four for hour - five for minute - six for second
I have two sets of these, the first set returns the start date and the second set returns the end date - if you type the date and time in a cell and do the same in another then you'll get the correct answer, so many years, months and etc. However in one cell I have a formula =DATEVALUE(G30 & "/" & J30 & "/" & D30) and in another cell have a formula =TIMEVALUE(M30& ":" & P30 & ":" & S30) now the formula I have used to combine the two CONCATENATE this is the formula -

=CONCATENATE(YEAR((D23))-YEAR(D2)-IF(OR(MONTH((D23))<MONTH(D2),AND(MONTH((D23))=MONTH(D2),DAY((D23))<DAY(D2))),1,0)&" YEAR(S), "&MONTH((D23))-MONTH(D2)+IF(AND(MONTH((D23))<=MONTH(D2),DAY((D23))<DAY(D2)),11,IF(AND(MONTH((D23))<MONTH(D2),DAY((D23))>=DAY(D2)),12,IF(AND(MONTH((D23))>MONTH(D2),DAY((D23))<DAY(D2)),-1)))&" MONTH(S), "&(D23)-DATE(YEAR((D23)),MONTH((D23))-IF(DAY((D23))<DAY(D2),1,0),DAY(D2))&" DAY(S), ",IF(HOUR(N23)-HOUR(N2)<0,HOUR(N23)-HOUR(N2)+24,HOUR(N23)-HOUR(N2))," HOUR(S), ",IF(MINUTE(N23)-MINUTE(N2)<0,MINUTE(N23)-MINUTE(N2)+60,MINUTE(N23)-MINUTE(N2)), " MINUTE(S), ",IF(SECOND(N23)-SECOND(N2)<0,SECOND(N23)-SECOND(N2)+60,SECOND(N23)-SECOND(N2)), " SECOND(S) ")

the answer is not correct, somehow it can't or doesn't work between the day and hour.

For example wed 22 feb 2012 20:36:00 and the other date is sat 22 feb 2014 20:35:00
the correct answer would be 1 years, 11 months, 30 days, 23 hours, 59 minutes, and 00 seconds
BUT using the datevalue and timevalue as explained the answer is not correct 2 years, 0 months, 0 days, 23 hours, 59 minutes, and 00 seconds

I have tried other formula's too, eg: =DATEDIF(A1,A2 -(MOD(A1,1)>MOD(A2,1)),"y")&" years, "&DATEDIF(A1,A2 -(MOD(A1,1)>MOD(A2,1)),"ym")&" months, "&DATEDIF(A1,A2 -(MOD(A1,1)>MOD(A2,1)),"md")&" days, "&TEXT(MOD(A2-A1,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds""") which this formula users date and time in one cell, the next formula users the date from one cell adn the time from another

=DATEDIF(A7,A15 -(MOD(A7,1)>MOD(A15,1)),"y")&" years, "&DATEDIF(A7,A15 -(MOD(A7,1)>MOD(A15,1)),"ym")&" months, "&DATEDIF(A7,A15 -(MOD(A7,1)>MOD(A15,1)),"md")&" days, "&TEXT(MOD(B15-B7,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds""")

I know there is a simple answer, but I just somehow either don't see it or I don't know

Many thanks