Two column types in one column work-around - #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 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
-
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!