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

Find more posts tagged with

Formulas

columns

Accepted answers

All comments

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

https://community.smartsheet.com/discussion/comment/438874#Comment_438874

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

https://community.smartsheet.com/discussion/comment/438874#Comment_438874

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.