Query changing dates to numbers and pulling data incorrectly

Ronderbecke

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
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?
 

Joe4

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

  • 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...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • 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...
  • 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#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top