Formula to Auto Populate Hybrid Schedule

Options

Hi,

CALLING ALL MY FORMULA GURUS!! πŸ˜€

I am building productivity metrics for our teams to give visibility to the level of work done when remote vs. in the office. Our schedules are set.

Week A: In Office Tuesday, Wednesday, Thursday

Week B: In Office Monday, Wednesday

I need to build a formula to auto-populate whether that day is remote or office. I have a Date Column from January 1 to present (by day), a Week A column, and a Week B column. These are WORKDAYS only and exclude HOLIDAYS.

Thanks in advance!

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

Best Answer

Answers

  • Katie G
    Katie G ✭✭✭✭
    Options

    How about something like this?

    =IFERROR(IF(AND([Week Type]@row = "A", OR(WEEKDAY(Date@row) = 3, WEEKDAY(Date@row) = 4, WEEKDAY(Date@row) = 5)), "in office", IF(AND([Week Type]@row = "B", OR(WEEKDAY(Date@row) = 2, WEEKDAY(Date@row) = 4)), "in office", "remote")), "")

  • Darla Brown
    Darla Brown Overachievers
    Options

    That would work if I did have a Week Type column. At this point, I don't have any identifiers for that. The date column is a running list of days added with data via Data Shuttle.

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I would suggest a reference table that has every day of the year going down a date type column. Then you can either manually enter or use a formula to populate a separate column with which week it is. The formula route would depend on a few variables, but the manual route would be as simple as manually entering the first couple of weeks and then copy/pasting it on down.


    Then you can leverage the reference table to quickly populate your main table of dates.

  • Darla Brown
    Darla Brown Overachievers
    Options

    Thanks, @Paul Newcome Let me try putting this together. I'll message if I hit a wall. πŸ˜‚

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Katie G
    Katie G ✭✭✭✭
    Options

    @Darla Brown ah, that makes sense! agree with what Paul suggested. May or may not help but a function that might help is MOD?

    If you use the MOD function to divide by two and get the remainder, you'll get 1 if it's odd and 0 if it's even. Then could map those to A and B, if it perfectly alternates. MOD(WEEKNUMBER(Day@row), 2)

    Of course would need extra thought around changes from one calendar year to the next.

  • Darla Brown
    Darla Brown Overachievers
    Options

    Thanks so much! I'm about to work on things now.

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Darla Brown
    Darla Brown Overachievers
    Options

    @Paul Newcome @Katie G

    So I've created a reference sheet:

    Date Column - full year minus weekends

    Day column - using this formula =IF(WEEKDAY(Date@row) = 1, "Sun", IF(WEEKDAY(Date@row) = 2, "Mon", IF(WEEKDAY(Date@row) = 3, "Tue", IF(WEEKDAY(Date@row) = 4, "Wed", IF(WEEKDAY(Date@row) = 5, "Thur", IF(WEEKDAY(Date@row) = 6, "Fri", IF(WEEKDAY(Date@row) = 7, "Sat")))))))

    Week Type column for Week A and Week B - manually typed 10 cells then copied down

    Now - I have this formula that works but it doesn't take into consideration Week B. I tried adding another statement for B using Thur and Fri as Office, but that didn't work.

    =IF(AND([Week Type]@row = "A", Day@row = "Mon"), "Office", IF(OR(Day@row = "Tue", Day@row = "Wed"), "Office", "Remote"))

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Katie G
    Katie G ✭✭✭✭
    Options

    @Darla Brown nice!

    You could try nexting an OR inside an AND... for example:

    AND([Week Type]@row = "B", OR (Day@row = "Thurs", Day@row = "Fri"))

    should get anything where it's week B and either Thurs or Fri

  • Darla Brown
    Darla Brown Overachievers
    Options

    Write it out like this?

    =IF(AND([Week Type]@row = "A", Day@row = "Mon"), "Office", IF(OR(Day@row = "Tue", Day@row = "Wed"), "Office", AND([Week Type]@row = "B", OR (Day@row = "Thurs", Day@row = "Fri"),"Office","Remote"))))

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“
    Options

    I think more like this should work:

    =IF(OR(Day@row = "Wed", AND([Week Type]@row = "A", OR(Day@row = "Tue", Day@row = "Thurs")), AND([Week Type]@row = "B", Day@row = "Mon")), "Office", "Remote")

  • Darla Brown
    Darla Brown Overachievers
    Options

    This worked! Thank you so much! Syntax is really important πŸ˜€

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!