How can I compare two dates and then add thirty days to whichever date is later?
Hello and first off thank you for your help.
I am trying to build a formula in a third column that will allow me to compare two date columns and then add thirty days to which ever date is later. example comparing "Release Date 1" to "Release Date 2" give me whichever date is later plus thirty days. Thank you so much for your assistance.
Best Answers
-
Is the column where you add the formula a Date Type Column?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Terrell.Boaz52986 I'm always happy to help!
I've modified the formula in your published sheet.
Did it work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi Terrell,
Try something like this.
=IF(OR(ISBLANK([Release Date 1]@row); ISBLANK([Release Date 2]@row)); ""; IF([Release Date 1]@row < [Release Date 2]@row; [Release Date 2]@row + 30; [Release Date 1]@row + 30))
The same version but with the below changes for your and others convenience.
=IF(OR(ISBLANK([Release Date 1]@row), ISBLANK([Release Date 2]@row)), "", IF([Release Date 1]@row < [Release Date 2]@row, [Release Date 2]@row + 30, [Release Date 1]@row + 30))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hey Andree,
Both of these options gave me #UNPARSEABLE. I have attached a screenshot of the sample sheet I am building it on and the formula I am using, am I doing something wrong? The rows with Item 1 and Item 2 are the same formula.
-
Ok.
Strange!
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hey Andree,
Here is a publish link for the sheet I was attempting to build it on. The formula will actually live on a different sheet but it contains a ton of confidential info so I figure this would be the easiest method. I am essentially trying to compare 2 release dates and then populate a third column with the earlier of the two dates +30 days.
https://app.smartsheet.com/b/publish?EQBCT=a3616e94df0848e089051ace282dd363
Thanks so much.
-
Happy to help!
I tested it before I shared it and it worked for me.
I'll take a look at the published sheet and get back to you.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Is the column where you add the formula a Date Type Column?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you so much, my third column was still a text column :( can't believe I missed that. Thanks for your help Andree.
-
@Terrell.Boaz52986 I have added another option to your published sheet in row 5.
=IF(AND(ISDATE([Release Date 1]@row), ISDATE([Release Date 2]@row)), MAX([Release Date 1]@row, [Release Date 2]@row) + 30)
If both cells contain a date, it will pull the most recent one and add 30 days to it.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
So this solution worked, I have a new question regarding this, is there an easy way I can trigger this formula based off another column?
Example, run this formula if the "Residual QC Status" column is = N/A
Thanks so much for your help with this process :)
-
@Terrell.Boaz52986 I'm always happy to help!
I've modified the formula in your published sheet.
Did it work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
It did work, thank you so much for you help :).
-
@Terrell.Boaz52986 Excellent!
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!