How can I compare two dates and then add thirty days to whichever date is later?

Terrell.Boaz52986 · 02/08/20

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.

Andrée Starå · 02/10/20

Is the column where you add the formula a Date Type Column?

Andrée Starå · 03/09/20

@Terrell.Boaz52986 I'm always happy to help!

I've modified the formula in your published sheet.

Did it work?

Andrée Starå · 02/10/20

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!

Terrell.Boaz52986 · 02/10/20

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.

Screen Shot 2020-02-10 at 9.48.49 AM.png

Andrée Starå · 02/10/20

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)

Terrell.Boaz52986 · 02/10/20

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.

Andrée Starå · 02/10/20

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.

Andrée Starå · 02/10/20

Is the column where you add the formula a Date Type Column?

Terrell.Boaz52986 · 02/10/20

Thank you so much, my third column was still a text column :( can't believe I missed that. Thanks for your help Andree.

Paul Newcome · 02/10/20

@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.

Andrée Starå · 02/10/20

@Terrell.Boaz52986

Easy to miss!

Happy to help!

Terrell.Boaz52986 · 03/09/20

@Andrée Starå

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 :)

Andrée Starå · 03/09/20

@Terrell.Boaz52986 I'm always happy to help!

I've modified the formula in your published sheet.

Did it work?

Terrell.Boaz52986 · 03/09/20

It did work, thank you so much for you help :).

Andrée Starå · 03/09/20

@Terrell.Boaz52986 Excellent!

Happy to help!

Formulas and Functions

Formulas and Functions

How can I compare two dates and then add thirty days to whichever date is later?

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Is there a way to make a field in a form auto populate street address as its being entered?

IF formula for INDEX/MATCH across sheets

NETDAYS Formula Question