Change Data in Columns According to Time Stamp

[damon_l]

Good Day,

I have a column with a date and time stamp every 15 minutes where data points have been logged.

I'm looking for a formula that will change the existing data points to 0 every day between 13h00 and 17h00 over a full 12 months. All other times outside that period the data will stay the same.

Anyone have an idea on how to do this?

Thanks
Regards
Damon

 

[mrhstn]

Hi

Assuming your timestamp is in column H and your data is in column I you could try this in a helper column

<timevalue("17:00")))*i2[ code]
=NOT((MOD(H2, 1) > TIMEVALUE("13:00"))*(MOD(H2, 1) < TIMEVALUE("17:00")))*I2

If the timestamp is exactly 13:00 or 17:00 then the datapoint will show, you need them to be 0 also then add an equals (=) to the greater/less than.

HTH</timevalue("17:00")))*i2[>

 

[damon_l]

Hi,

I ended up using this formula according to the following format.

=NOT((MOD(C2; 1) > TIMEVALUE("12:30"))*(MOD(C2; 1) < TIMEVALUE("17:00")))*M2

And it has worded well.......however I need to expand this formula to now change the values between 17:30 and 21:30 to 3.48, recall I'm running a time sequence every 15 minutes for a full 12 months.

I need to ensure all values between 17:30 and 21:30 are set to 3.348

I have the following formula in column M =IF(D14=0; 0; 3,348)

I tried referencing a 2nd formula following the initial one but am not getting the expected results.

Thanks
Regards
Damon