Formula to Auto Populate Hybrid Schedule
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
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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")), "")
-
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
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
-
@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.
-
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
-
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
-
@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
-
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
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This worked! Thank you so much! Syntax is really important 😀
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!