Calculate dates based on IF condition

Hello Forum,

I need some help in calculating the (4) different dates based on a base date. These dates are required for the California Fiscal Year, which starts from
present year 07/01/xx to following year 06/30/xx.

I need a conditional formula in the below two scenarios,

1) Base date falling in the 2nd half (07/01/xx to 12/31/xx) of the year.

If base date is 08/23/2012, the (4) calculated should be

1st start 09/01/2012
1st end 12/31/2012

2nd start 01/01/2013
2nd end 06/30/2013

2) Base date falling in the 1st half (01/01/xx to 06/30/xx) of the year.

Eg1:

If base date is 02/14/2014, the (4) calculated should be

1st start 03/01/2014
1st end 04/30/2014

2nd start 05/01/2014
2nd end 06/30/2014

Eg2:

If base date is 01/14/2014, the (4) calculated should be

1st start 02/01/2014
1st end 04/15/2014

2nd start 04/16/2014
2nd end 06/30/2014

Hope you have understood my question and please let me know for more inputs on this.

Attached is the my sheet with half success.

See attached. Dates Calc2.xlsx
Here we work on "dd/mm/yyyy" whereas at it seems to be "mm/dd/yyyy" at your location. But the formulas should work OK in all cases. Let us know if anything.

Hi jewel & cplettner, thanks for your time on providing the formulas

The formulas that you provided are below

1st Start
by C
by J

Please Login or Register  to view this content.

1st End
by C
by J

Please Login or Register  to view this content.

2nd Start
by C
by J

Please Login or Register  to view this content.

2nd End
by C
by J

Please Login or Register  to view this content.

After testing these, the formulas by (J)Jewel has fulfilled my purpose for

1st Start
2nd Start
2nd End

However, in 1st End formula, some changes are needed in 'False logical Test'

Please Login or Register  to view this content.

It would be great if you guys could provide a formula where in the date arrived, could divide the '1st start' & '2nd End' in two equal halves.

For Example,
1st start date is 02/01/12 & 2nd end date is 06/30/12, there is a difference of 5 months.(Each half of 2.5 months)
Could i get 1st End as 04/15/12, so that the following 2nd start would be 04/16/12.
Another example,
1st start date is 03/01/12 & 2nd end date is 06/30/12, there is a difference of 4 months.(Each half of 2 months)
Could i get 1st End as 04/30/12, so that the following 2nd start would be 05/01/12.

I hope this above examples would explain cpletter's doubt

But if the base date is in the first half you have two examples one when the base date is in Feb and one when it is in Jan. I'm not quite sure why 1st end date changes form 4/30 to 4/15. What is the overarching rule?

Please let me know for more questions you have!!!! Thanks soo much on this...