Setting up an Office Status Indicator

Christopher Neal

Hello!

I am trying to setup a sort of "office status" dashboard for staff. Can anyone guide me on a good way to do this? I am a bit stumped as to how I can get some of the info across.

I have one sheet with PTO information:

• PTO Start (Date)
• PTO End Date (Date)
• Currently on PTO (True , False)
• Name
• Request Duration (Whole Day, Custom)

I have another sheet that is a list of all of the Staff

I would like to make a formula or helper columns to show a symbol column of red, yellow, green for their status.

• Red = Currently on PTO = True, Request Duration= Whole Day
• Yellow - Currently on PTO = True, Request Duration= Custom
• Green = Currently on PTO = False

Genevieve P.

Hi @Christopher Neal

The way I would do this is to use a COUNTIFS formula to count the number of rows with your criteria. If there's even 1 row that matches, you know that it's true so you can say "Red" or "Yellow". If no rows match your criteria, then it must be "Green" because no one is on PTO today.

For example, to look for "Red", try:

=IF(COUNTIFS({Name}, Name@row, {Currently On PTO}, "True", {Duration}, "Whole Day") >= 1, "Red",

Then we can repeat the same thing for Yellow but swap out the "Whole Day" to say "Custom":

IF(COUNTIFS({Name}, Name@row, {Currently On PTO}, "True", {Duration}, "Custom") >= 1, "Yellow",

Full Formula:

=IF(COUNTIFS({Name}, Name@row, {Currently On PTO}, "True", {Duration}, "Whole Day") >= 1, "Red", IF(COUNTIFS({Name}, Name@row, {Currently On PTO}, "True", {Duration}, "Custom") >= 1, "Yellow", "Green"))

I will note that if you're using the TODAY function in your other sheet to return either "True" or "False" for if they're currently on PTO, you'll want to make sure that source sheet is saved each day so that it recognizes what day "Today" is. (See: TODAY Function)

Cheers,

Genevieve