Formula help

[onewingdove]

I attached a small clip of the cells I'm working with.

Ignoring the spacing between columns, let's call them Column A, Column B and Column C

What I'm trying to do is if:

Column A has a date, then Column C = Column A+7 days
Column A is blank, but Column B has a date, then Column C = Column B+7 days
If both Column A and Column B are blank, the Column C = blank

Closest I've been able to get:
=IF(ISBLANK(A1), B1+7, A1+7)

However, this does not account for BOTH being blank, which is what I'm trying to figure out.

There does not need to be a calculation if both Column A and Column B have dates, as only Column A or Column B are filled and never both

 

[Peter_SSs]

Welcome to the MrExcel board!

From your description columns A & B will never both have a date on the same row. If that is so, then try something like this

=IF(A2&B2="","",SUM(A2:B2)+7)

Edit: I have just looked more closely at your sample data & suspect that your dates are not real dates (numbers) but text values. Excel dates only start at 1900.
If you point this formula at one of your date cells, what does it return?

=ISNUMBER(A2)

 

[Peter_SSs]

Assuming dates are text and could be before 1900, see if this works for you.

25 02 01.xlsm
	A	B	C
1
2		1/20/1899	1/27/1899
3
4	2/10/1899		2/17/1899
5		12/28/1865	1/4/1866
6	3/30/1984		4/6/1984
Dates

Cell Formulas
Range	Formula
C2:C6	C2	=LET(mdy,TEXTSPLIT(A2&B2,"/"),yr,TAKE(mdy,,-1),t,DATE(yr+2000,INDEX(mdy,1),INDEX(mdy,2))+7,IFERROR(TEXT(t,"m/d/")&yr+(YEAR(t)-2000-yr>0),""))

 

[HongRu]

Try it.

=IF(ISBLANK(A1), IF(ISBLANK(B1),"",B1+7), A1+7)

 

[Peter_SSs]

Try it.

=IF(ISBLANK(A1), IF(ISBLANK(B1),"",B1+7), A1+7)

Did you try it with the sample data the OP gave?

 

[HongRu]

Did you try it with the sample data the OP gave?

I didn't assume dates are text and could be before 1900, because OP said:

Closest I've been able to get:
=IF(ISBLANK(A1), B1+7, A1+7)

Your formula is perfect, but mine is simple and maybe what OP wants. ^^

 

[Peter_SSs]

I didn't assume dates are text and could be before 1900,

4 out 5 of their examples are dates before 1900 and therefore must be text. Given that sample data I would be very surprised if all of their data consisted of dates after 1900.

My question was simply did you try your formula with their sample data.

 

[HongRu]

4 out 5 of their examples are dates before 1900 and therefore must be text. Given that sample data I would be very surprised if all of their data consisted of dates after 1900.

My question was simply did you try your formula with their sample data.

You're right.
By the way, why you add "(YEAR(t)-2000-yr>0)" in your formula ?

And, why not "index(mdy, 3)" instead of "TAKE(mdy,,-1)" ?

Thank you

 

[Peter_SSs]

why you add "(YEAR(t)-2000-yr>0)" in your formula ?

So that I still got the correct year if adding 7 days put the date into the next year like in row 5 of my mini sheet.

why not "index(mdy, 3)" instead of "TAKE(mdy,,-1)"

No particular reason. It is just what came into my head at the time.

 

[HongRu]

So that I still got the correct year if adding 7 days put the date into the next year like in row 5 of my mini sheet.



No particular reason. It is just what came into my head at the time.

You are so great.
I didn't consider the possible of the next year.
Your answers are always well thought out and without any logical flaws.
Thanks for your reply.