Need Help, Nested If Date formula

I am trying to streamline a sheet that is used for project payment forecasting. The goal is to use the project's estimated ship date, when 40% of the project total is due, and have the 40% amount cell be copied into the corresponding date columns. I have a column for each month going to the end of the year. The sheet is a bit cluttered but I use reports to organize data in a neater way.

what I am trying to accomplish is, if the [ESTIMATED SHIP DATE] cell is not blank & is a date, then take [INVOICED AMOUNT] from the same row and copy it into the correct month column.

Find more posts tagged with

Accepted answers

Pnda

Worked perfectly, THANK YOU!

All comments

Andrée Starå

Hi @Pnda

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info:

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Have a fantastic week & Happy Independence Day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Pnda

Thanks for the info @Andrée Starå.

I dont think the automation will work for my application because I need the [Row Total] value to go into the correct date column based on the [Est Project Ship Date]

in the screen grab below you can see the ROW Total, Est Ship date, I would like help writing a formula to copy the row total into the corresponding month columns to the left of the EST SHIP column based on the date in the EST SHIP DATE column for each row

Paul Newcome

All you need is an IF formula that you would tweak for each month.

=IF(IFERROR(MONTH([EST SHIP DATE]@row), 0) = 1, [ROW TOTAL]@row)

The above would go in the January column. Changing the 1 to a 2 will update it for February. 3 would be for March, so on and so forth.

Pnda

Worked perfectly, THANK YOU!

Paul Newcome

Happy to help. 👍️

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.