How to Add an absolute reference AND have a column formula without cross sheet references
Hi
Starting at "Part 1 Calc column Formulae are
=DATE([Year of next Event Adjusted for New Year]1, [Month of Next Event Adjusted if >12]1, 1)
=(NETDAYS([Reference date of a Monday (Used for Calc 2)]@row, [First Day of "Month of Next Event" Part 1 of calc]@row) - (7 * INT(NETDAYS([Reference date of a Monday (Used for Calc 2)]@row, [First Day of "Month of Next Event" Part 1 of calc]@row) / 7)) + 1)
=IF([Weekday of 1st Day of Month Part 2 of calc]1 = 1, [First Day of "Month of Next Event" Part 1 of calc]1 + 1, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 2, [First Day of "Month of Next Event" Part 1 of calc]1 + 0, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 3, [First Day of "Month of Next Event" Part 1 of calc]1 + 6, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 4, [First Day of "Month of Next Event" Part 1 of calc]1 + 5, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 5, [First Day of "Month of Next Event" Part 1 of calc]1 + 4, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 6, [First Day of "Month of Next Event" Part 1 of calc]1 + 3, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 7, [First Day of "Month of Next Event" Part 1 of calc]1 + 2)))))))
=([First Monday of Month Part 3 of calc]1 + [Weekday of recurrence. Sunday = Day 1]1 - 2)
=IF(DAY([First Actual Weekday in Month Part 4 of calc]1) > 7, [First Actual Weekday in Month Part 4 of calc]1 - 7, [First Actual Weekday in Month Part 4 of calc]1)
=IF([Week of Recurrence in Month]@row = 0, [First Day of "Month of Next Event" Part 1 of calc]@row, [Day Corrected if beyond 7 Part 5 of Calc]1 + (([Week of Recurrence in Month]1 - 1) * 7))
=[Actual Next Event Date Part 6 of calc]@row - TODAY()
The above sheet calculates for each row an event date and emails an owner (in a column not seen here) when due. It worked perfectly when I had the reference positioned in one of the other columns but this would have necessitated cells being dragged down when a new row is added.
As a result I repositioned the reference and I have converted most formulae to column formulae but am getting syntax errors when trying to refer to the first cell in the right hand column. I use this cell to figure out what day of the week it is in the 9th column to the left. I want to avoid using cross sheet references as it will confuse the users. (I wont be around to explain) I looked at using a workflow to add the fixed date into every row but that isn't allowed either.
What is allowed that might help. please?
Best Answers
-
Got it to work. Sometimes in formulating a question, you are forced to think. I changed the formula to eliminate the reference by putting a fixed date like follows. I am sure I could make it more elegant but it is working for me it appears.
=(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) - (7 * INT(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) / 7)) + 1)
-
Andrée Starå ✭✭✭✭✭✭I hope you're well and safe!
Excellent! Glad you got it working!
Be safe, and have a fantastic day!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅Please support the Community by marking your postwiththe accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Answers
-
Another thought is Can I add a date into the formula directly instead of a reference as it is the same date for every row?
-
Got it to work. Sometimes in formulating a question, you are forced to think. I changed the formula to eliminate the reference by putting a fixed date like follows. I am sure I could make it more elegant but it is working for me it appears.
=(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) - (7 * INT(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) / 7)) + 1)
-
Andrée Starå ✭✭✭✭✭✭
I hope you're well and safe!
Excellent! Glad you got it working!
Be safe, and have a fantastic day!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅Please support the Community by marking your postwiththe accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Categories
You would use the below for Jan 2023 and adjust the month and year numbers accordingly for each of the other months.<\/p>
=IF(AND(MONTH([Start Date]@row)<= 1, YEAR([Start Date]@row)<= 2023, MONTH([End Date]@row)>= 1, YEAR([End Date]@row)>= 2023), [$ per month]@row)<\/p>"},{"commentID":387901,"body":"
Lets try a different approach.<\/p>
=IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH(Start Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/105523/\") + MONTH([Start Date]@row))<= 202307<\/strong>, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/105523/\") + MONTH([End Date]@row))>= 202307<\/strong>), [$ per month]@row)<\/p>
<\/p>
Basically we are creating a yyyymm stamp from the start and end dates and comparing them to the yyyymm stamp for that year\/month combo. The above is for July 2023 (202307).<\/p>"},{"commentID":387906,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/G1SDKXPQ7I1D\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/GCQ7U05Q4GNO\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/79GD9D2EWXCW\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":268,"urlcode":"Attachments","name":"Attachments"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"},{"tagID":510,"urlcode":"email-notifications","name":"Email Notifications"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":4892,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
