Query changing dates to numbers and pulling data incorrectly

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>
DayDate8:00 AM10:00 AM12:00 PM2:00 PM4:00 PM6:00 PM8:00 PMAvailability
Saturday8/31/201943711 10:00 AM
Sunday9/1/201943708 43709 43710
Monday9/2/2019
Tuesday9/3/201910:00 AM2:00 PM
Wednesday9/4/2019
Thursday9/5/2019
Friday9/6/2019
Saturday9/7/20192:00 PM
Sunday9/8/2019
Monday9/9/2019
Tuesday9/10/2019
Wednesday9/11/2019
Thursday9/12/2019
Friday9/13/2019
Saturday9/14/2019
Sunday9/15/2019
Monday9/16/2019
Tuesday9/17/2019
Wednesday9/18/2019
Thursday9/19/2019
Friday9/20/2019
43711 10:00 AM43708 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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I have never use Query in Excel before, so really cannot help there. But I can help you with some understanding how Excel functions:
Also its converting all the dates to number format and I can't seem to stop it from doing that?
Excel actually stores all dates and times as numbers, specifically the number of days since 1/0/1900. The number just has a Format to display it as a date.
This can easily be seen by entering any date into any cell in Excel. Then change the format to General, and you will see the date as Excel does.
Note that changing the format does NOT actually change the value - they are the same. It just changes how it is displayed.

Time is just the fractional component of one day.
So 6:00 AM is equivalent to 0.25.
12:00 PM is equivalent to 0.5
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top