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

Himesh

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

Nic Larsen

You could create a Helper Column for each month and put the date in each of those columns. Then change your current column back to text/number and run the formula. Just hide the helpers if you don't need them.

Himesh

Nic Larsen

Jun 3, 2024

You could create a Helper Column for each month and put the date in each of those columns. Then change your current column back to text/number and run the formula. Just hide the helpers if you don't need them.

Thanks, this could be a solution. But if I drag the formula across columns (i.e. April 2024, May 2024, June 2024), is there a way for the formula to replicate across automatically with the helper column?

Himesh

Nic Larsen

Jun 3, 2024

You could create a Helper Column for each month and put the date in each of those columns. Then change your current column back to text/number and run the formula. Just hide the helpers if you don't need them.

Thanks, this could work, but if I replicate the formula across columns (e.g. April 2024, May 2024, June 2024), is it possible to have the formula match the helper column's months? Or will each month column need to be set up to match the helper column row manually?