Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Format dates inside a add-date & IF formula

Topic Labels: Formulas
Solved
Jump to Solution
1646 2
cancel
Showing results for 
Search instead for 
Did you mean: 
myrte_van_der_v
4 - Data Explorer
4 - Data Explorer

Hello,

For my plant tracker I’ve been using a formula to give me an estimated date of transplanting. This has been working fine. Now I want to add a check-box, so that when I plant something in-ground and i check this, the Estimated date of transplanting box will return an N/A vallue. I got all of this to work but I have a problem with how to date is formatted.

Screenshot 2022-11-08 at 10.16.35

As you can see the last column has a different date format. This is the code:

IF({In-ground} = 1, “N/A”, IF({Germination Date}, DATEADD({Germination Date},{Estimated days to Transplanting}+0,‘days’),IF({Estimated germination date}, DATEADD({Estimated germination date},{Estimated days to Transplanting}+0,‘days’))))

I tried to add a BLANK() after the DATEADD so I can change it in the formatting tab but it doesn’t work. I think I have to add a DATETIME_FORMAT but i’m not sure where to add it in this formula.

Thanks for your help!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

I think you’ll need to wrap all the dates in your formula with DATETIME_FORMAT like you said, resulting in something like this:

IF({In-ground} = 1, "N/A", IF({Germination Date}, DATETIME_FORMAT(DATEADD({Germination Date},{Estimated days to Transplanting}+0,"days"), "LL"),IF({Estimated germination date}, DATETIME_FORMAT(DATEADD({Estimated germination date},{Estimated days to Transplanting}+0,"days"), "LL"))))

Can’t be sure this formula will work as I can’t test it easily; feel free to DM me a creator link to your base and I can just do it for you if you’d like

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

I think you’ll need to wrap all the dates in your formula with DATETIME_FORMAT like you said, resulting in something like this:

IF({In-ground} = 1, "N/A", IF({Germination Date}, DATETIME_FORMAT(DATEADD({Germination Date},{Estimated days to Transplanting}+0,"days"), "LL"),IF({Estimated germination date}, DATETIME_FORMAT(DATEADD({Estimated germination date},{Estimated days to Transplanting}+0,"days"), "LL"))))

Can’t be sure this formula will work as I can’t test it easily; feel free to DM me a creator link to your base and I can just do it for you if you’d like

Thank you! It worked.