Ronderbecke
Board Regular
- Joined
- Oct 4, 2017
- Messages
- 73
Currently I am trying to figure out why Query is changing the information and pulling on cells it shouldn't be? I have a table setup which is this:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="177"></colgroup><tbody>
</tbody>
The query function should be pulling on cells with numbers in both the date field and time field. When there is spacing between them; however, it pulls random dates for some reason? Also its converting all the dates to number format and I can't seem to stop it from doing that? Here is my current formula:
=IFERROR(QUERY({ArrayFormula({C2:C22}&" "&{G2:G22}),G2:G22},"Select Col1 Where Col2<>''"),"")
In this instance, Date is column C on the sheet. Did I mess something up with this formula?
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Day | Date | 8:00 AM | 10:00 AM | 12:00 PM | 2:00 PM | 4:00 PM | 6:00 PM | 8:00 PM | Availability |
Saturday | 8/31/2019 | 43711 10:00 AM | |||||||
Sunday | 9/1/2019 | 43708 43709 43710 | |||||||
Monday | 9/2/2019 | ||||||||
Tuesday | 9/3/2019 | 10:00 AM | 2:00 PM | ||||||
Wednesday | 9/4/2019 | ||||||||
Thursday | 9/5/2019 | ||||||||
Friday | 9/6/2019 | ||||||||
Saturday | 9/7/2019 | 2:00 PM | |||||||
Sunday | 9/8/2019 | ||||||||
Monday | 9/9/2019 | ||||||||
Tuesday | 9/10/2019 | ||||||||
Wednesday | 9/11/2019 | ||||||||
Thursday | 9/12/2019 | ||||||||
Friday | 9/13/2019 | ||||||||
Saturday | 9/14/2019 | ||||||||
Sunday | 9/15/2019 | ||||||||
Monday | 9/16/2019 | ||||||||
Tuesday | 9/17/2019 | ||||||||
Wednesday | 9/18/2019 | ||||||||
Thursday | 9/19/2019 | ||||||||
Friday | 9/20/2019 | ||||||||
43711 10:00 AM | 43708 43709 43710 |
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="177"></colgroup><tbody>
</tbody>
The query function should be pulling on cells with numbers in both the date field and time field. When there is spacing between them; however, it pulls random dates for some reason? Also its converting all the dates to number format and I can't seem to stop it from doing that? Here is my current formula:
=IFERROR(QUERY({ArrayFormula({C2:C22}&" "&{G2:G22}),G2:G22},"Select Col1 Where Col2<>''"),"")
In this instance, Date is column C on the sheet. Did I mess something up with this formula?