Collecting data from multiple rows in one cell

  • I have a workbook that has a start time and end time on two rows in multiple. I am trying to collect all the data in a macro so I can manipulate it.


    One row example

    06:21

    17:05


    When I try and collect it it using VBA just gives me "06:21. But if I go to the worksheet and copy it into another cell (eg =B6) then it gives me 06:2117:05 which is what I want to collect using the macro!


    Can anyone please help as it is driving me crazy!


    Many thanks

    Ian

  • You need to understand how times are stored in Excel. They are not as they appear, Excel always stores dates and times the same way. Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.


    How are you using the result?


    You can use the Text Function to copy them exactly to your cell, assuming times in A3 and A4


    =TEXT(A3,"hh:mm") &TEXT(A4,"hh:mm")

  • Thanks Roy


    The only issue is that the two times are in the same cell - so not A3 and A4 - but just A3. I have screenshotted a a small part of the spreadsheet - attached - to show. So in this case if I add FullTime=cells(3,1) I only get "06:59 shown - not the rest of the contents and not the second speech marks.


    I was expecting 06:5917:28 or "06:5917:28"


    Many thanks

    Ian

  • Thanks again.


    So that removes the wrapping which is great - thanks very much, And the cell shows as just one string - eg 06:5317:07 - but the program still only picks up the 06:53. See below for the screenshot and the code I am using.


    Sorry to be a pain but it is causing much head scratching and stopping me completing my program. Thanks again for all of your help.


    Cheers

    Ian

  • Thanks Roy - see attached.


    The plan is:


    1) Collect the start time and end time from the cell

    2) Calculate the total time worked

    3) Round up or round down

    4) Display all data on different tabs depending on the department

    5) Finally look for anomalies where there is only a check in time and create a list to be manually adjsued.


    I am happy with all the steps as long as I can get a variable with the full data in it! I am manipulating as numbers as find that easier for what I want to do.


    Many thanks

    Ian


    TimesheetProgram.xlsm

  • You don't need the loop to remove the WrapText, this will remove WrapText from all cells


    Code
    Cells.WrapText = False

    What is this for? Stop will halt the code


    Code
    x = 1
    Cells(7, 35) = Cells(6, 2)
    Stop
  • Hi Roy


    It does remove all wrapped text from all cells but unfortunately doesn't solve the problem.


    When run, Cells (7.35) shows the text wrapped again (even after all cells having wrapping removed )

    And the value collected in the code is still the same "06:57 - it won't collect all of the information.


    If you run the code on the attached which includes your changes, you will see what I mean.


    Thanks

    Ian


    TimesheetProgram.xlsm

  • This calculates the time for one cell- C6.


  • Thanks Roy. Sorry I missed the question.


    The times are collected by a fingerprint machine when staff arrive and leave for work - it is used to calculate their hours so they can be paid correctly.


    Thanks for the code - which is excellent - I just need to solve the problem with the display of the data and I can use it.


    Cheers

    Ian

  • Forgive me - that works - many thanks. Was the problem that i was trying to collect the information as a string?


    Cheers

    Ian

  • That's why I use TimeValue to convert. to times.


    I don't know what you are doing exactly but you will need to loop through each cell to record the times, probably stick to minutes then convert the total to hours and minutes. Attach an example of where the times are going.


    Obviously, even better would be to get the times reported correctly.

  • This calculates the time for one cell- C6.


    RoyUK,


    Can you modify your VBA code to work with hour after 23:59.


    Say in A2:A54 are data like this:

    06:30 A2

    15:25

    ---------

    21:45 A3

    06:30

    --------

    22:30 A4

    05:45

    -------

    ------=

    13:35 A54

    23:20


    and result will be in cells (B2:B54)

    Thank you.

  • Try this


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!