Problem with Index(date+time)

[DavidRoger]

Hi,

=Index(date,F1)+index(time,F1)

didn't produce same result as Index(date) and Index(time) done separately.

Tried below and get the date right but the time wrong.
1)

=text(index(date,F1),"m/d/yy")+text(index(time,F1),"h:mm")

2) Set in cell format, m/d/yy h:mm

date = A1:A10 (In m/d/yy)
time = B1:B10 (In h:mm)

How to combine both date and time with index?

[JohnTopley]

Can you post a small file showing expected results.

[DavidRoger]

Please have a look. Cheers.

[ChemistB]

I used the two formulas in Column G and H and they give the same results. See attached.

In G4
=IF(F4="","",INDEX($A$4:$A$103+$B$4:$B$103,F4))

In H4
=INDEX($A$4:$A$103,F4)+INDEX($B$4:$B$103, F4)

[JohnTopley]

I experienced the same problem as David but applying "Chemistb" formulae it worked!

[ChemistB]

I also got the correct answer when, in I4, I used this

=TEXT(INDEX($A$4:$A$103,F4),"m/d/yy")+TEXT(INDEX($B$4:$B$103,F4),"h:mm")

If you upload a sheet showing an incorrect result, I can tell you why.

[JohnTopley]

The original file supplied by David produces the wrong result (or may be won't with your "magic"!)

[ChemistB]

Nope, all looks good to me. ::::::Scratching head:::::::::::::

Did you pay extra for the "bug free" version of Excel?

[DavidRoger]

Even my original file shows all is good when I opened again. My working sheet still showing the wrong result. In my working sheet, I use the named range. Tested it in the sample file, it show the correct result. Kind of strange, it is?

[DavidRoger]

Chemist,

Have a look at this.

[ChemistB]

Starting with row 11225, the dates include a time. Even though it's formatted as a date, the time still exists so when you add it to the time in Col B, it gives an incorrect answer. That's what's throwing everything off. I checked Column B and the reverse is not true, all the times are time only.

To fix this in your formulas
=INT(Date)+time

[DavidRoger]

Now the data is good. Before this, I tried to use the date+time in a chart but the chart went haywire. Tried again this day. The chart reads all the time as 0:00.

[ChemistB]

Are we talking about the time attached to the date in Column A or the separate times in Column B? If it's B, are they actually reading as 0:00 or are they just summihng as 0?