Formula to Auto Populate Hybrid Schedule

Darla Brown

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!

Paul Newcome

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")

Katie G

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

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.

Paul Newcome

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

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

Katie G

@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

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

Darla Brown

@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"))

Katie G

@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

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"))))

Paul Newcome

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

This worked! Thank you so much! Syntax is really important 😀

Paul Newcome

Happy to help. 👍️