damonl

2018-01-15, 12:25 PM

Good Day,

I have a large spreadsheet with multiple columns taken from a data logger.

The first column is a timestamp column that logs data every 15 minutes for a full 12 months.

I needed to modify this column to reset all data logged between 12:00 and 17:00 so I used this formula.

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

In Column M I used the following formula

M =IF(D14=0; 0; 3,48)

As the data logged is binary only a 0 or 1 but the figures needs to be either a 0 or 3.48 to be of any value to me.

So the initial formula states that data logged between 12:00 and 17:30 must be reset to 0.

However, I need to expand on this formula by maybe adding another formula or condition to reset all the data between 17:00 and 21:00 to 3.48. Original data logged during these times are either 0 or 3.48 at the moment but need to change them all to 3.48.

Are there any excel guru's out there that can assist?

Thanks

Regards

Damon

I have a large spreadsheet with multiple columns taken from a data logger.

The first column is a timestamp column that logs data every 15 minutes for a full 12 months.

I needed to modify this column to reset all data logged between 12:00 and 17:00 so I used this formula.

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

In Column M I used the following formula

M =IF(D14=0; 0; 3,48)

As the data logged is binary only a 0 or 1 but the figures needs to be either a 0 or 3.48 to be of any value to me.

So the initial formula states that data logged between 12:00 and 17:30 must be reset to 0.

However, I need to expand on this formula by maybe adding another formula or condition to reset all the data between 17:00 and 21:00 to 3.48. Original data logged during these times are either 0 or 3.48 at the moment but need to change them all to 3.48.

Are there any excel guru's out there that can assist?

Thanks

Regards

Damon