# Formula help = if date contains TBD, leave blank

✭✭

Hello!

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

I need to have the formula calculate:

1. A date from the entered work week;
2. be left blank if no work week is entered
3. 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!

Tags:

• ✭✭✭✭✭✭

Try this:

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

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭

@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!

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Try this:

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

• ✭✭✭✭✭✭

Nice catch. I missed the TBD part.

Did that work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
edited 08/01/22

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 = "", "")

• ✭✭✭✭✭✭

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?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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))

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭

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

Thank you so much!

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭

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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!