How to move information with a workday function?

Justin Ramos

I work for a bath remodeling company and we have installers with level 1, 2, 3 based off skillset. I need a way to plan availability based off the amount of installers we have and their levels. Right now the date column reads, =WORKDAY(TODAY(0), Label@row) and this allows the dates to update automatically. I originally had a formula all the way down to automate the availability, but we need the ability to open up the calendar or restrict it so I input in the numbers you see here and an index/collect formula to show planned against what has been submitted on the calendar and will show the availability by date and level (what you see below). However, if I go the next day, the information stays and the date moves. How can I move the number with the workday formula? I tried to work with ProDesk and they weren't able to help me so I am hoping someone from the community has a work around!

Nick Burrus

Your WORKDAY will always consider TODAY. Because you used the reference TODAY, so you can view next year, but your formula says TODAY. Why is your date column (TODAY(0)) to begin with?

You can easily just select a beginning date and use simple math like laid out here: =Date1 +1 for Tuesday then for Wednesday =Date2 + 1 it'd easily allow you to continue to go downwards automatically with this formula. When you hit Sunday, simply make Sunday's +2.

https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates

Justin Ramos

@NBurrus I used the TODAY function with the label column that projects out the number of days based off of the current day to automatically update everyday. If I selected a beginning date, it would go in the past which I don't need because I need it to update the dates everyday to follow our calendar. Also, I still need to determine the workaround for the other information for the levels planned. I need the planned level numbers to follow the movement of the date column because right now it is a number that doesn't move if that makes sense.

Nick Burrus

https://community.smartsheet.com/discussion/comment/297495#Comment_297495

Your descriptions are pretty vague. Do you know how to do a technical storyboard? https://www.storyboardthat.com/blog/e/what-is-a-storyboard

Break out exactly what you need with screenshots, and perhaps a concept of what your end point is desired to look like. You're asking for a lot of levels in one, break them out individually and get 1 of them to work first and we can start layering on more and more, and make it more complex. Start at the foundation and work your way up.

So question 1: Your =WORKDAY already works perfectly?

Question 2: Does WORKDAY still show correctly in the calendar (not the other features just workday)?

If your answer to 2 is no, then your system may be a bit bugged. Possibly try using ISDAY as well https://help.smartsheet.com/function/isdate

Question 3: What do you need beyond the workday, the rest of what you're saying doesn't make much sense. TRy to simplify it the best you can, and use real world examples and column names. An example sheet for example would work too with screenshots.

Question 4: What exactly do you mean by "move the number"?