Need some help on a formula

[watkins365]

Sorry, i am probably being a bit dense but cannot get a formula to work.
Column J is a blank in which user inserts a date
Column L is a blank in which user might insert a date
Column O is blank BUT needs a formula that is the date inserted in J plus 5 days but only if there is no date in L, if there is a date in L then column O must be L plus 5 days .

Been going around in circles on this !

 

[Arts]

Is it possible to have a date in both column L and J and if so which date takes precedent if not then maybe

=IF(L2="",J2+5,L2+5)

 

[Joe4]

Welcome to the Board!

Try:

=IF(AND(J5="",L5=""),"",IF(L5>0,L5+5,J5+5))

 

[watkins365]

,

Is it possible to have a date in both column L and J and if so which date takes precedent if not then maybe

=IF(L2="",J2+5,L2+5)

many thanks, away from PC presently. Yes date in both possible. If their is a date in J then that takes precedent.

 

[Joe4]

Column O is blank BUT needs a formula that is the date inserted in J plus 5 days but only if there is no date in L, if there is a date in L then column O must be L plus 5 days .

Yes date in both possible. If their is a date in J then that takes precedent.

OK, that seems to contradict your original statement, in which it implies if there is a date in L, that would seem to take precedence.
You also did not mention if it is possible if both might be blank. I have the formula returning a blank if both J and L are blank.

If that is possible, and you want to return a blank, and J really does take precedence over L, you would just flip-flop the J and L references in the second IF statement, i.e.

=IF(AND(J5="",L5=""),"",IF(J5>0,J5+5,L5+5))

 

[watkins365]

OK, that seems to contradict your original statement, in which it implies if there is a date in L, that would seem to take precedence.
You also did not mention if it is possible if both might be blank. I have the formula returning a blank if both J and L are blank.

If that is possible, and you want to return a blank, and J really does take precedence over L, you would just flip-flop the J and L references in the second IF statement, i.e.

=IF(AND(J5="",L5=""),"",IF(J5>0,J5+5,L5+5))

My apologise, I was away from the spreadsheet when I answered. If there is a date in L then that is the date that will require 5 days to be added. I have flipped the J and L, tried the formula suggested and it works providing there is a date in both J and L, if there is only a date in J it is giving me a random 1900 year date

 

[Arts]

Seems to work ok for me, any chance you could post a screenshot

 

[watkins365]

Seems to work ok for me, any chance you could post a screenshot

View attachment 121512

This is the formula as it stands ( I am On Row 6 rather than 5)

This is producing

It is resulting in this if only one date shown

But if L has a date it results in this, which is correct but need it to have a correct date in the above scenario as well.

I am sure its something I am doing so do forgive me ignorance!

 

[Joe4]

This is the formula as it stands ( I am On Row 6 rather than 5)
View attachment 121517
This is producing

It is resulting in this if only one date shown
View attachment 121518

But if L has a date it results in this, which is correct but need it to have a correct date in the above scenario as well.

View attachment 121520

I am sure its something I am doing so do forgive me ignorance!

You have not typed the formula I gave you in post 5 correctly!
Look again closely at the column references.
In the second IF, you are checking column J for a value, and if it has one, you are adding the values to column L! You have those last two column references switched!

I usually recommend NOT manually typing out the formula we give you, as that can lead to typos like this.
Just use copy and paste, and then you can simply change the row number in the formula.

 

[Arts]

The formula is wrong (as in copied over incorrectly from what Joe provided), in the second IF

You have: =IF(AND(J5="",L5=""),"",IF(J5>0,L5+5,J5+5))

It needs to be: =IF(AND(J5="",L5=""),"",IF(J5>0,J5+5,L5+5))