Forum Discussion

Sheriffg84's avatar
Sheriffg84
Copper Contributor
Jan 10, 2020
Solved

Count By Color with Conditional Formatting

What I'm trying to do is quickly and automatically figure out how many employees I have scheduled for each quarter hour. I am so close to completing this project but I have one issue to overcome. I h...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 14, 2020

    Sheriffg84 

    You may mark it as a "Best solution". Then you'll make me really happy. Haha!

Sheriffg84's avatar
Sheriffg84
Copper Contributor
Jan 14, 2020

I found an issue with both of our solutions. Example is that if I have an employee scheduled 8 am to 4 pm with each cell being  by the quarter hour what is happening is its adding an extra 1 in the cell that's technically the 4 pm to-4:15 pm cell. the cell that is labeled 3:45 pm would technically be 3:45 pm to 4 pm. Any thoughts or suggestions on how to deal with this?

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 14, 2020

Sheriffg84 

Have a look at the attached workbook. It's the one I used earlier to test your "problem". 8am to 4pm equals to 8 hours, i.e. 32 quarters. In my schedule, 32 columns are coloured, the last one being 3.45pm, marking the beginning of the last quarter. I believe that's correct.

  • Sheriffg84's avatar
    Sheriffg84
    Copper Contributor
    Jan 14, 2020
    I see what I did wrong. You're a genius!!! Thank you so much for your help. I really do appreciate it

Resources