Getting Excel to recognize all data points between sets of time.

Question

Hello,

I'm working with a data set that has information every 15 minutes over the course of a year. I'm trying to create a function that recognizes values from different columns, and performs a different action based on the time of day.

In plain English what I'm looking to do is this: If N10>10 and R9>=R6 and its Monday-Friday 10:00am - 7:59pm then "DISCHARGE", if N10<-10 and r9<R5 and its Monday - Friday 8:00pm-9:59am or its any time on Saturday and Sunday then "CHARGE", if it's none of these, then "IDLE"

I started with IF(AND functions, and was able to get this this function to work with no problems: =IF(AND(N10>10,R9>=$R$6),"DISCHARGE",IF(AND(N10<-10,R9<$R$5),"CHARGE","IDLE"))

When I try to add in my time column and the times I want, it just defaults to IDLE, which led me to believe my time format was incorrect, but I have verified the time format is correct, I've changed the time format. I've retyped the time into different formats, none of which seems to be working. Not only do I want(need) the times to work, but the end formula should also include the days of the week. I have my times broken out into their own column (B) and the days of the week in column (C).

The closest I've been able to get without an error is: =IF(AND(N10>10,R9>R6,B10:B28284>=TIME(10,0,0),B10:B28284<=TIME(19,59,0)),"DISCHARGE",IF(AND(N10<-10,R9<R5,B10:B28284>=TIME(20,0,0),B10:B28284<=TIME(9,59,0)),"CHARGE","IDLE"))

I've tried with TIME in the function as written above, I've tried by putting the time range in a cell and referencing the cell, I've tried typing "10:00:00" in the formula, but nothing seems to be working. When I put my cursor into my formula and follow it out the logical tests, and values line up correctly, but again, it is only returning an IDLE response.

I also tried a multitude of IFS variations instead of IF(AND, but I couldn't get them to work either, but I obviously can't get anything to work, so it's possible I just had the formula for my IFS functions wrong as well.

As I've been working on this one problem for over 2 days any bit of help would be beyond appreciated.

Answer 1

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.