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
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!