Forum Discussion
Formula Help
Hello,
I've currently got Excel reading a list of times and plotting these times from the list into the correct category and time line, however if the start and finish times spread over 2 hours or more I need it to still read this hour between as a valid value, an example being the start time is 13:45 and the finish time is 15:23 I want excel to know that 14:00 is between these 2 times and give some kind of value, the same if the start time was 13:45 and finish time 16:10 for it to know 14:00 and 15:00 are between these times.
my current formula which works with the times from the list is below, Red being the table of times from the list and green being the times by hour, the times on the list change daily.
=IF(SUMIFS(AP$25:AP$45, AP$25:AP$45, ">="&$AO12, AP$25:AP$45, "<"&$AO13),1,0)
Does any of this make sense? Top table is where its plot the start and finish times from the bottom 2 tables but its leaving the times between these 2 times blank.
You did not tell us which version of Excel you are using, or on which platform (Windows, Mac…).
The main problem is that your SUMIFS formulas are comparing the start hour and end hour to the entire range of scheduling data (rows 25 to 45) of values, rather than to the rows in the appropriate section (end hour should be compared to rows 25 to 35 for Start data, and start hour should be compared to rows 36 to 45 for Finish data).
See the attached workbook for multiple solutions.
You did not tell us which version of Excel you are using, or on which platform (Windows, Mac…).
The main problem is that your SUMIFS formulas are comparing the start hour and end hour to the entire range of scheduling data (rows 25 to 45) of values, rather than to the rows in the appropriate section (end hour should be compared to rows 25 to 35 for Start data, and start hour should be compared to rows 36 to 45 for Finish data).
See the attached workbook for multiple solutions.
SnowMan55 Thank you very much for your rapid response and support. Your sumifs solution has worked instantly.
I'm building this on my Mac, but it will mostly be used on Windows at work I am not sure of what version it has, but it seems relatively new, most of the work is done on the web through a share point so I would assume this side of it is 365, so needs to work across all platforms, which I hope it does after all this.
Do you have any guides on mass conditional formatting, is there any quick way of doing this? The table you have helped resolve needs to reflect these values on another sheet that will be used daily, when there is a one value, I need the cells to be clear, but if the value is zero, I need the cells to be blacked out.
As you can see below, I have started doing each block, but this needs replicating 12 times per hour for 13 hours to read each cell in the first table, so screens 1 to 3 are blacked out, and 4 is open and clear.
I've tried searching online, but what I have seen doesn't work with grouped cells like I have, and unfortunately, I cannot change the layout of the sheet.
Any guidance would be greatly appreciated.
Thank you
Martin
The merged cells do add some complexity to the conditional formatting. See the _Info worksheet of the attached workbook for a description of my technique.
In the future, either attach a sample workbook (with sensitive/proprietary data replaced or removed, of course) to the post. Or load the file to Microsoft OneDrive, Google Drive, DropBox, or another file sharing service, set the security to make it readable by everyone, and include a link to that copy in your post. You should not expect other forum members to convert your image(s) to a workbook.
