Setting up an Office Status Indicator

Options

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


Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!