PDA

View Full Version : Change Data in Columns According to Time Stamp Using Multiple Formulas or Conditions



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

NBVC
2018-01-15, 12:50 PM
Perhaps something like:

=IF(AND(C2>=TIME(12;30;0);C2<TIME(17;0;0));0;IF(AND(C2>=TIME(17;0;0),C2<TIME(21;0;0));3,48;0))

damonl
2018-01-15, 01:24 PM
I have a separate column that says either run or stop and I converted this to binary in a new column, either 0 or 1 hence the formula M =IF(D14=0; 0; 3,48) and the original formula including the multiplication of this output. This has to be included as only cells between 12:00 and 17:00 should be set to 0 and17:30 and 21:00 should be set to 3.48. Outside those hours the run and stop criteria equalling either 0 for Stop or 3.48 for Run…..hope that makes sense!!

I figured out the formatting issue with your formula as it was giving me a formatting error but the formula is resetting all cells with 3.48 outside the above hours with 0.

damonl
2018-01-15, 08:28 PM
The above formula actually sets all time stamps to 0 even time stamps in the 17:30 to 21:00 region, when the should be 3.348 and all other time stamp values should stay as is?

damonl
2018-01-16, 09:42 AM
Any other ideas on what formula to use to accommodatedata in both time frames only?

NBVC
2018-01-16, 01:40 PM
I still don't entirely understand the dilemma. Can you post a sample workbook to show what you have and what you expect as results?

damonl
2018-01-16, 05:25 PM
I have attached a sample file spanning a 24 hour period, you can see the existing formula working between 12:00 and 17:00 setting all values to 0, all cells outside this period stay as is. I need to expand the existing formula to set all values in columns K to M to 3.48 between 17:30 and 21:00.

NBVC
2018-01-16, 07:24 PM
Your dates in column C are all text strings, so when you use MOD(C2,0) the result is a #VALUE! error.... so not sure what you are accomplishing.

Perhaps you can use the time in column A instead since that is where column C time is coming from....

My formula would become:

=IF(AND($A2>=TIME(12,30,0),$A2<TIME(17,0,0)),0,IF(AND($A2>=TIME(17,0,0),$A2<TIME(21,0,0)),3.48,0))*H2

copied down and across.

damonl
2018-01-16, 10:01 PM
Thanks the formula seems to do half the job as sets allvalues to 0 between 12:00 and 17:00 and all values to 3.48 between 17:30 and 21:00,however I had to omit the last part of the formula *H2 to get it to work this way.


But it also resets all values outside those timeframes to 0,is there a way to say if outside those hours to leave the values as is referringto columns H, I and J where the originals values are stored?

p45cal
2018-01-16, 11:07 PM
=IF(AND($A2>=TIME(12,30,0),$A2<TIME(17,0,0)),0,IF(AND($A2>=TIME(17,0,0),$A2<TIME(21,0,0)),3.48,0))
try changing the bold red 0 to the cell ref. in the column you're wanting to get the value from.