Formula For Dates

[Keshypops]

Good Day

I please need some help,

I am looking for a formula that will pull through a set date, if the dates chosen fall between certain dates. E.g below:

The rules are: 1 Jan 2010 to 30 June 2010 - should pull through 30 June 2016
1 July 2010 - 30 June 2011 - should pull through 30 June 2017
1 July 2011 - 30 June 2012 - should pull through 30 June 2018
And so on.

My question is if 1 April 2010 is select as the date, what formula do I use to check if 1 April 2010 is between 1 Jan 2010 and 30 June 2010 in order to give me 30 June 2016?

Please Help?

The below is what I have, though does not seem to be working

=IF(AND(E1>=DATE(2010,1,1),E1<=DATE(2010,6,30)),DATE(2010+6,6,30),IF(AND(E1>=DATE(2010,7,1),E1<=DATE(2011,6,30)),DATE(2010+7,6,30),IF(AND(E1>=DATE(2011,7,1),E1<=DATE(2012,6,30)),DATE(2010+8,6,30),IF(AND(E1>=DATE(2012,7,1),E1<=DATE(2013,6,30)),DATE(2010+9,6,30),IF(AND(E1>=DATE(2013,7,1),E1<=DATE(2014,6,30)),DATE(2010+10,6,30),IF(AND(E1>=DATE(2014,7,1),E1<=DATE(2015,6,30)),DATE(2010+11,6,30)))))))

[Glenn Kennedy]

Hi there. This will do what you jneed.

[Vikas_Gautam]

Got the solution..

Check the attachment..
Don't forget to click *

[Keshypops]

Thanks for the Help, though I am getting a #N/A

[Glenn Kennedy]

Can you be a bit more specific? Which solution(s) have you tired? Which one is giving #N/A? What input caused this error?

[Keshypops]

Apologies Glenn, your solution does not work.

The lookup solution from Vikas could work, However how can it work with the lookup value being from a linked cell?
The date is selected using a date & time picker, which is then linked to a cell.

[Vikas_Gautam]

Just change the lookup_Value argument in Lookup Function .. to the base cell of Date and time picker..

Vikas Gautam

[Glenn Kennedy]

You didn't meantion the date picker before... Try this.

[cbatrody]

Hi,

Please try the following formula in B1, considering that you have the dates in A1:

=IF(MONTH(A1)<=6,DATE(YEAR(A1)+6,6,30),DATE(YEAR(A1)+7,6,30))

[Keshypops]

Thanks to all for the help greatly appreciated.

Glenn, Thank You your solution works

[Glenn Kennedy]

You're welcome. It'd also be nice if you also clicked the Add Reputation button at the foot of the post(s) of those who helped provide the soluion you wnated.