Query changing dates to numbers and pulling data incorrectly


New Member
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
Tuesday9/3/201910:00 AM2:00 PM
Saturday9/7/20192:00 PM
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>

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?


MrExcel MVP, Junior Admin
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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...