If cells b5, d5, and/or g5 are empty (one or all), run a formula to calculate a due date

[eabaker64]

Hi. I need a formula in B3 to look at cells B5, D5 and G5. If any of those cells are blank, a formula is run which calculates a date that is 15 workdays after a date showing in cell B2. If cells B5, D5 and G5 all contain data, the formula does not run and instead returns a value of N/A in cell B3. I have this formula but if any of the B5, D5, or G5 cells have data, it returns N/A. I don't want it to return N/A unless all three cells contain data. Any ideas?

=IF(B5<>"","N/A",IF(D5<>"","N/A",IF(G5<>"","N/A",IF(B2<>"",WORKDAY(B2,15),""))))

 

[dreid1011]

Give this a try:

Book1
	A	B	C	D	E	F	G
1
2		7/1/2024
3		N/A
4
5		xyz		123			abc
Sheet1

Cell Formulas
Range	Formula
B3	B3	=IF(COUNTA(B5,D5,G5)<3,WORKDAY(B2,15),"N/A")

 

[eabaker64]

Give this a try:

Book1
	A	B	C	D	E	F	G
1
2		7/1/2024
3		N/A
4
5		xyz		123			abc
Sheet1

Cell Formulas
Range	Formula
B3	B3	=IF(COUNTA(B5,D5,G5)<3,WORKDAY(B2,15),"N/A")

OMG, you are awesome!!! It has taken me all day not to be able to figure out what it took you a very short time to do. Thank you so much!!!

 

[dreid1011]

OMG, you are awesome!!! It has taken me all day not to be able to figure out what it took you a very short time to do. Thank you so much!!!

You're welcome, and happy to help.

 

[eabaker64]

Okay, one more question. I have attached the spreadsheet so you can see what I am trying to do. The formula you gave me is in B7 (1st follow up due to send back and works perfectly. G9 has not been filled in so the formula calculates the 15 days and gives me another due date. Now I want it to do the same thing in D7 (calculate a due date 10 working days) or N/A if G9 is filled in, but I don't want it to remove the due date in B7. How can I keep the due date in B7, but have it show N/A in D7 because a date has been entered in G9? Thank you for your assistance - greatly appreciated!

 

[dreid1011]

Are you able to use the XL2BB mini sheet addon or can you at least take a screenshot with row/column information? I can't tell what is what by your pictures.

 

[eabaker64]

Are you able to use the XL2BB mini sheet addon or can you at least take a screenshot with row/column information? I can't tell what is what by your pictures.

 

[dreid1011]

Okay, so if G9 is filled in, the formula in B7 says it should return "N/A" (because B9 and D9 are also filled in). But you want the date to stay in B7 even though B9, D9, and G9 are filled in? If so, this is going to require a VBA approach instead of formula, because you can only have 1 value in a cell at a time, either a formula or a hard coded value.

 

[eabaker64]

Okay, so if G9 is filled in, the formula in B7 says it should return "N/A" (because B9 and D9 are also filled in). But you want the date to stay in B7 even though B9, D9, and G9 are filled in? If so, this is going to require a VBA approach instead of formula, because you can only have 1 value in a cell at a time, either a formula or a hard coded value.

Okay. I see what you are saying. Thank you for your assistance, I really do appreciate it. I might have to make some changes to the spreadsheet.

 

[eabaker64]

I have not worked with VBA for several years and even then, it was hunting through forums and videos to find what I needed. I am not having as much luck with this in trying to figure out a VBA code to do what I want. Any ideas? And if not, thank you for all you have done!