I’m looking to create projection columns for each month of the year by taking the Value column divided by the Duration (In Months). I’d like this column to be $0 if a project isn’t expected to start yet or has already ended which is why I included two date related conditions using Start Date and End Date. Unfortunately, I’m getting an Invalid Formula Error; has anyone found a fix for something similar? Not sure what I’m missing! Thanks in advance!
Hi Laura,
Try removing the = in front.
Also it looks like your second >= should be <=. Just a guess.
Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com
Hi Gilles,
Thank you! I removed the = and updated the signs and still can’t get anything other than 0 to populate. I then tried a few other formulas using both my Start Date and End Date columns and can’t get anything other than 0; no matter what the formula and what date I select, the date doesn’t pass the criteria. Do you know if date columns are incompatible with IF formulas?
Last attempt below, I removed the AND to make the formula simpler, still no luck!
Thank you!
Laura
Hi @lauramagiera,
Your IF comparison is wrong.
Set your syntax to {Start Date} only and you’ll see that the result will be something like 2025-03-14 (may vary based on your computer settings)
Now set it to DATE(2025,1,1) and you’ll get something like Web Jan 01 2025 00:00:00….
So, your comparison is wrong because the dates are not formatted the same way.
Try:
IF(AND({Start Date}>="2025-01-01"
Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.
Thank you SO MUCH! This fixed the issue for me- you are a life saver!