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:

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!

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!

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!

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

Julie ZC

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

Paul Newcome

Happy to help. 👍️