Formula help = if date contains TBD, leave blank

Julie ZC

Hello!

I'm struggling with the 3rd part of my formula.

I need to have the formula calculate:

A date from the entered work week;
be left blank if no work week is entered
and leave blank if TBD is listed in the work week column

1 & 2 work using this formula:

=IF(ISBLANK([Work Week]@row), "", (DATE(year@row, 1, 1) + 7 * ([Work Week]@row) - 8))

Everything I've tried has not yet worked - can you help?

Thanks!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=IF(OR([Work Week]@row = "", [Work Week]@row = "TBD"), "", (DATE(year@row, 1, 1) + 7 * ([Work Week]@row) - 8))

Andrée Starå

@Julie ZC

Excellent! (It was a collaborative effort with Paul)

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

All comments

Andrée Starå

Hi @Julie ZC

I hope you're well and safe!

Try something like this.

=IF([Work Week]@row = "", "", DATE(YEAR(Year@row), 1, 1) + (7 * [Work Week]@row) - 8)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

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!

Julie ZC

@Andrée Starå - this is giving me the error #invalid data type

I feel like this should be so simple, which is making me go nuts!

Andrée Starå

@Julie ZC

Happy to help!

Strange!

Ensure that the Year column is a date and the Work Week isn't.

Was that the issue?

If not, can you paste the formula you used?

✅Remember! 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!

Paul Newcome

Try this:

=IF(OR([Work Week]@row = "", [Work Week]@row = "TBD"), "", (DATE(year@row, 1, 1) + 7 * ([Work Week]@row) - 8))

Andrée Starå

@Paul Newcome

Nice catch. I missed the TBD part.

@Julie ZC

Did that work?

Julie ZC

Oh I appreciate your help!

The Year is text, not date (there isn't a full date for this, just the year)

When I use ONLY the first part of your formula, it works

=IF([Work Week]@row = "", "")

So I have 3 separate working formulas, but I can't seem to merge them:

=DATE(year@row, 1, 1) + (7 * [Work Week]@row - 8)

=IF(ISBLANK([Work Week]@row), "")

=IF([Work Week]@row = "", "")

Andrée Starå

@Julie ZC

Try this. (make sure that the Year column is a date column)

=IF(OR([Work Week]@row = "", [Work Week]@row = "TBD"), "", DATE(YEAR(Year@row), 1, 1) + (7 * [Work Week]@row) - 8)

Did that work?

Andrée Starå

@Julie ZC

Or this if you want to keep the Year column as is.

=IF(OR([Work Week]@row = "", [Work Week]@row = "TBD"), "", (DATE(Year@row, 1, 1) + (7 * [Work Week]@row) - 8))

Julie ZC

YOU DID IT!!!!!!!!!! @Andrée Starå

Thank you so much!

Andrée Starå

@Julie ZC

Excellent! (It was a collaborative effort with Paul)

You're more than welcome!

Julie ZC

@Paul Newcome thank you for your help in this!!!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.