I want to add a column that lists dates 6 months from other date column
Have tried a few different formulas and not working
Best Answer
-
Carson Penticuff ✭✭✭✭✭✭You can try putting this in. It is just the same formula with the IFERROR() removed. If that results in errors in those same cells, the date in that specific row may not be formatted correctly.
=DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row))
Answers
-
Carson Penticuff ✭✭✭✭✭✭
=IFERROR(DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row)),"")
This one should work. You will need to change [First Date] to whatever your date column name. You will also need to ensure that both the [First Date] column and the column you want the +6 month date to appear in are both formatted as dates. The IFERROR() is in place in case there are any blank cells somewhere in the column.
-
Thanks Carson, that worked!
But its still not dropping in for some cells in the column and im not sure why?
-
Carson Penticuff ✭✭✭✭✭✭
You can try putting this in. It is just the same formula with the IFERROR() removed. If that results in errors in those same cells, the date in that specific row may not be formatted correctly.
=DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row))
-
Worked! Thanks Carson!
-
Carson Penticuff ✭✭✭✭✭✭
Happy to help, I'm glad it worked!
-
Hi again,
another issue - if the +6 months runs into the next year, its coming up as an error. Any idea how to solve this?
-
Carson Penticuff ✭✭✭✭✭✭
I failed to take into account wrapping into the next year. The first formula is for one month into the future, the second is for six months.
=IF(MONTH([First Date]@row) = 12, DATE(YEAR([First Date]@row) + 1, 1, DAY([First Date]@row)), DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row)))
=IF(MONTH([First Date]@row) > 6, DATE(YEAR([First Date]@row) + 1, MONTH([First Date]@row) - 6, DAY([First Date]@row)), DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row)))
-
Worked - Thank you!!
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/106748/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
