Share via

Return values between time range

Anonymous
2024-03-20T20:58:38+00:00

Hi, I'm struggling to produce an idea of how to return value and then run certain calculations.

In the example below, I have two columns. Time and numbers

  • first, I would like to return values between 11:00:00 and 12:00:00, all the way down (the Tiem column represents 5-minute intervals of 24 hours),

To be more specific I would like to return values for each day, between 10:00:00 and 11:00:00

  • second, out of the returned values, I would like to return to the next columns only values that are greater than the top cell.

(the table below is an example of a few thousand data points that represent days)

10:50:00 2369.2
10:55:00 2368.3
11:00:00 2370
11:05:00 2369.2
11:10:00 2368.2
11:15:00 2370.9
11:20:00 2370.2
11:25:00 2366.3
11:30:00 2367
11:35:00 2367.8
11:40:00 2371
11:45:00 2368.7
11:50:00 2367.3
11:55:00 2374.2
12:00:00 2373.3
12:05:00 2367.8
12:10:00 2367.8
12:15:00 2364.4
12:20:00 2361.9
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

46 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2024-03-24T07:06:36+00:00

    I think that the best way would be if you just pick one month and concentrate on that.

    I can't understand why it's switches month

    31/01/2024 23:55:00
    02/01/2024 00:00

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-03-24T06:41:23+00:00

    I appreciate that you have patience with me!

    Well, I'm not a data analyst or programmer.

    I don't do anything with data; I don't convert dates.

    I've found a program that downloads data series from my broker, but it looks like there is some error in it. . For some unknown reason, for me, it's changing formatting from month to month. 

    Can we focus on peace of data, which is ok, ignoring the rest?

    In the meantime, I'll try to figure out other sources of data or what the issue with the code is.

    This data I've downloaded this morning.

    Book1.xlsx

    Was this answer helpful?

    0 comments
  4. Anonymous
    2024-03-24T01:54:49+00:00

    Things get worse before they get better!

    Dates are still wrong in column A, but I welcome that they contain time and date data in one cell.

    If you are trying to convert text to dates yourself, well.. don't! (If you are pasting, paste into Text formatted cells.)

    There are some gaps too; look around row 71958 to 71998.

    A text file from TS (or whatever) is best.

    Column B is formatted as time but they seem to be prices.

    Can you confirm columns B:D are in the order: open. close, high, low?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2024-03-23T21:22:24+00:00

    I apologize I didn't know it would mess up the shared online

    I have pulled the data once again from TS

    TS.xlsx

    Was this answer helpful?

    0 comments