MOD function with a time field

[H28Sailor]

Hi there



I currently collect a huge amount of Forex tick data and wish to analyse it.

Firstly I put all the records into time buckets of 40 minutes using the following:-

=A2-MOD(A2, 40/24/60) where A2 holds a time stamp e.g. 12:34:58

So far so good...................

Now I wish to reduce two and ONLY TWO of the time periods to 35 minutes using the following formula:-

=IF(OR(AND(A2>="12:00:00",A2<"12:40:00"),AND(A2>=" 16:00:00",A2<"16:40:00")),A2-MOD(A2, 35/24/60), A2-MOD(A10, 40/24/60))

This seems to result in the two periods I am trying to reduce being reduced to 35 minutes but other seemingly random periods also get reduced and the whole thing is a bit of a mess!

Any ideas?

Bob M

[H28Sailor]

In the last formula it should read MOD (A2...... not MOD (A10.........
Sorry

Bob M