google sheets, use formula output for next formula

0
votes

I'm trying to CONCATENATE two cells in order to compare the results so that I can search by them, however the values of the two CONCATENATE outputs are different as one inputs is coming from the another formula.

Screen shots attached enter image description here

I'm basically trying to compare the start time and channel number from A and B, with the data from G and H, so that I can update D with the relevant information in F (in the same format as A).

I first convert the EPOC time to human time readable, but when i try and CONCATENATE with the channel number, I get a different value to when i do that with A and B.

enter image description here formula for c2 =CONCATENATE(A2,B2) formula for i2 =G2/86400000+date(1970,1,1) formula for k2 =CONCATENATE(G2,H2)

As you can see, the values for c2 and k2 are different event though a2 and i2 are the same (looking).

I've tried using CELL, INDEX, and INDIRECT but just can't seem to get it right, and I've tried various formatting options

Hopefully i've explained this right. Any solution welcome

raw data csv here START ,CHANNEL,concat,end?,,EndDateTime epoc,startDateTime epoc,channel,converted start,converted end,concat 12:58:00 AM,10,,,,1520391600000,1520382480000,7,,, 12:28:00 AM,7,,,,1520395200000,1520384280000,10,,,

1
google-sheets

1 Answers

2
votes

So you have a couple of issues here.

  1. CONCATENATE(A2,B2) will never equal CONCATENATE(I2,H2) because the values in A2 (12:58) and B2 (10) do not equal the values in I2 (12:28) and H2 (7). I think you meant to compare A2,B2 to I3,H3

  2. A2 (12:58) does not equal I3 (12:58). You'll see this for yourself if you convert both to the date or number formats. The date value of A2 is 12/30/1899, the default when you enter only a time in the cell. The date value of I3 is 3/7/2018, because you converted the exact date and time from the EPOCH value.

For the two concatenations to equal each other, you need to resolve the issues above. You can do this by adding a date to column A's values.

On another note, I think there are better ways of populating column D based on the data in column F. A simple Vlookup should do the trick, once you resolve issue #2 above.