Two column types in one column work-around - #DATE EXPECTED error

Options
Himesh
Himesh
edited 06/03/24 in Formulas and Functions

Hi,

I'm in the process of transferring a spreadsheet into smartsheet that splits costs by months, and I've ran into a #DATE EXPECTED error for my formula. I can't change the column type as the formula itself requires the cell above to be a date.

Does anyone know if there's any work-around? Here's the formula: =IF(AND([April 2024]1 >= [Start Date]@row, [April 2024]1 <= [End Date]@row), [80%]@row / [Duration (Months)]@row, IF([April 2024]1 = DATE(YEAR([End Date]@row), MONTH([End Date]@row) + 3, 1), [20%]@row, ""))

I've included a screenshot of the sheet below.

Thanks,

Himesh

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!