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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2024-04-08T16:52:19+00:00

    thank you very much

    Was this answer helpful?

    0 comments
  2. 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

  3. Anonymous
    2024-04-02T00:38:26+00:00

    It looks like Excel is not the best tool for such analysis, and definitely it is above my capabilities right now. Although I thank you for your time and effort, I appreciate it.

    You're right. If you're with Tradestation, you've got some good strategy testing tools. See

    https://help.tradestation.com/10_00/eng/tradestationhelp/st_testing/strategy_testing.htm

    May I lastly ask you how did you corrected the date formats ?

    In the link you supplied in your message in this thread of 23 Mar 2024 10:22:24 PM to TS.xlsx (hopefully:
    https://1drv.ms/x/s!AnQVWPhytqkwga0hvM-BdIhtd-rYNw?e=qOrlp8 ) I've done the following:

    1. Changed the formatting of column A to General
    2. Scrolled down to the vicinity of row 2290 where you can see a mix of text and numbers in column A, where:
    • the text values are unconverted data which Excel failed to convert
    • numbers which Excel has converted from text to dates... only it's got them ALL wrong (months/days the wrong way round)

    So: see the formula in cell I2286:

    =IF(ISNUMBER(A2286),DATE(YEAR(A2286),DAY(A2286),MONTH(A2286))+MOD(A2286,1),DATEVALUE(A2286)+TIMEVALUE(A2286))

    which I've copied down for 10 or so cells. You need to copy this up and down for the whole range of data.

    This formula says: If column A is a number then swap days and months, otherwise convert the text to date and time using DateValue and TimeValue.

    This works well for me here in the UK but it (DateValue) may not work in your locale, so check that the results are correct (the dates/times should flow consecutively). Then only if all is OK, copy/paste-values from column I to column A over the existing data.

    If the results are not OK, come back and I'll have a workaround.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2024-04-01T18:45:54+00:00

    It looks like Excel is not the best tool for such analysis, and definitely it is above my capabilities right now. Although I thank you for your time and effort, I appreciate it.

    May I lastly ask you how did you corrected the date formats ?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2024-03-27T00:39:24+00:00

    However, I would like to test the specified time interval at a specific time of the day. So, let's say 50 minutes each day at the same time of the day. For example, start at 09:00 and end at 09:50 every day.

    See linked-to file below.

    Long trades only.

    Sheet1: Specify start time in cell D1, length in cell F1

    Result (green) table at cell H2. Needs refreshing like a pivot table: right-click somewhere in the green table and choose Refresh.

    It lists only trades which could be exited at a profit, even if that happens in the same bar as the entry (as long as that bar opens at its low).

    Sheet2: Previous 'every-n-minutes' solution as specified in cell F1 of Sheet1. Refresh to update it.

    The workbook: https://app.box.com/s/9ywmgjqp087jzc3kr9zdiyjxtjf2thsw

    I wasn't expecting it will be so complex and difficult

    I agree, those formulae are very difficult to adjust/maintain, hence the attached.

    Was this answer helpful?

    0 comments