Results 1 to 2 of 2

Thread: Query changing dates to numbers and pulling data incorrectly
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Query changing dates to numbers and pulling data incorrectly

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

    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?

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,797
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Query changing dates to numbers and pulling data incorrectly

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •