Two column types in one column workaround  #DATE EXPECTED error
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 workaround? 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

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?

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?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!