Consulting

Results 1 to 5 of 5

Thread: How do I search a spread sheet for a time

  1. #1

    How do I search a spread sheet for a time

    I have a spreadsheet with a times in a column in the "hh:mm" format.
    I want to find the 21:45 time period in vba and insert it into a function as a variable.

    So far I have
    Dim NYClose As Date (is that correct to Dim a time as Date?)
    NYClose= "21:45"

    Am I going about this correctly?
    What do I need to do to the "21:45" to make the NYClose variable the "21:45" time period I am looking for?

    Thank you

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Excel store date/times as decimal numerical values, with the date being encoded to the left of the decimal and the time encoded to the right of the decimal. Excel then displays said encoded date/time decimal numbers as indicated by the Format Cells dialog. depending on Excels settings and how the date/Time was entered into the cell, Excel can autoformat the displayed value.

    "21:45" might be a Date/Time value displayed thusly or it might be a String value displayed exactly as stored.

    Which is important to know for using "Find." Temporarily Format the cell as a number. If the value displayed changes to a number less than 1, (0.nnnnnnnn,) the value stored is a Date/Time of only the Time. A number larger than 1, (nnnnn.nnnnnnnnnnn) is a Date/Time with both Date and Time encoded. A number that has no decimal part, (nnnnn.00000000000,) is a Date/Time with only the Date encoded, [or the Time part is exactly Midnight, *00:00:00.000*].) Finally... If the display does not change, ie: remains 21:45, the Stored value is a String. Note any value displayed with a Colon in it can not be a numerical value, it can only be a Date/Time or a String.

    If 21:45 is a Date/Time with only the Time part encoded
    Dim NYClose As Date 
    NYClose= CDate("21:45:00")
    If 21:45 is a String
    Dim NYClose As String
    NYClose= "21:45"
    IF the displayed Time you are searching for is actually a Date/Time with both Date and Time parts encoded. It is a different situation and you should include a Date in the Searched for value
    Dim NYClose As Date 
    NYClose= 'a specific Date and Time
    IF the column you are searching has values like "2020:03:12-21:45:00" then
    Dim NYClose As String
    NYClose= "21:45"
    And use LookAt:=xlPart in the Find function
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Thank you SamT very comprehensive. I hope I can put it into practice.

  4. #4
    VBAX Newbie
    Joined
    Mar 2020
    Posts
    3
    Location
    Use CDate Function and the look at xlPart to search for dates

  5. #5
    Thank you Binu

Posting Permissions

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