How to join datevalue and timevalue into one

[quintin]

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</day(d2)),-1)))&"></month(d2),and(month((d23))=month(d2),day((d23))<day(d2))),1,0)&">

 

[Rick Rothstein]

Just add the cell with the date to the cell with the time. So if A30 has the formula...

=DATEVALUE(G30 & "/" & J30 & "/" & D30)

in it and B30 has the formula...

=TIMEVALUE(M30& ":" & P30 & ":" & S30)

in it, then just put this into C30 (or wherever you want it)...

=A30+B30

but don't forget to apply a cell format that display date and time values in that cell.

 

[quintin]

WOW, Thank you so much. Sometimes we analyse things to much and don't see the simple side of it

 

[Rick Rothstein]

WOW, Thank you so much. Sometimes we analyse things to much and don't see the simple side of it

The reason adding them together works has to do with how Excel stores dates... to Excel, date-times are floating point numbers... the whole number part contains the number of days offset from the day before January 1st, 1900 (this offset is the date part) and the fractional part is the fraction of a 24-hour day represented by the time. So, if the date is a whole number and the time is a fraction, adding them together gives you a complete date-time value.