Date help, within 60 days of Aug 6

Experts,

I might be able to figure out how to do this but I dont want to run the risk of relying on myself.  :)
I need to modify the below to show 60 days after Aug 6

=DATE(YEAR(NOW()),CHOOSE(MONTH(NOW()),IF(DAY(NOW())>15,7,1),7,7,7,7,7,IF(DAY(NOW())>15,13,7),13,13,13,13,13),15)

the above shows the 15 of either Jan or July

thank you
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
You may be over thinking this or I don't fully understand what you are trying to accomplish. If you only need the date that is 60 days from 8-6, put that date in a cell (A5 in my example) and use =A5+60 in B5 or where ever you wan to display the value 10-5-2014.

Days is the default date value in adding / subtracting dates. If you want any other value it is a little more complex, but here is a good tutorial.


http://office.microsoft.com/en-us/excel-help/add-or-subtract-dates-HP010342155.aspx
0
Glenn RayExcel VBA DeveloperCommented:
First, 60 days after August 6 is ALWAYS October 5.  Once you know that it's easier to construct the formula.

Going back to your previous questions regarding cutoff, the presumption here is that you want to return a fixed cutoff date (i.e., 60 days after Aug 6, or Oct 5) if the current date is less than or equal to August 5.

However, unlike your previous questions, you're not providing a second cutoff date.  Presumably, this may be six months away (February 6), but that's not clear.   What, for example, should the result be if today's date was November 1?

-Glenn
0
pdvsaProject financeAuthor Commented:
Hello once again, Glenn, for this one there is not a second date.  It is just simply 60 days after Aug 6.  

Jerry:  you have been a little out of the loop in my previous questions.  I was sure that either Glenn or Philip would be responding.  I think its a little more difficult but maybe you are right I am thinking too much into it.  I can say that I can not reference a cell within the formula.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

pdvsaProject financeAuthor Commented:
quite possibly it is this?
DATE(YEAR(NOW()),8,6)+60
0
Glenn RayExcel VBA DeveloperCommented:
According to your formula, November 1 - or ANY date in 2014 would return October 5.  Is that correct?

Or, should dates after October 5 yield October 5 of the following year?
0
pdvsaProject financeAuthor Commented:
Actually if it is past Oct 5 then it should be the following year.  I did not catch that.
0
Glenn RayExcel VBA DeveloperCommented:
Okay, then you could modify your formula like so:
=DATE(YEAR(NOW())+IF(NOW()>DATE(YEAR(NOW(),10,5),1,0),8,6)+60

or
=IF(NOW()>DATE(YEAR(NOW()),10,5),DATE(YEAR(NOW())+1,10,5),DATE(YEAR(NOW()),10,5)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Perfect.  I used the second one.  The first one gave me an error of too many arguments and highlighted the 10.
0
Glenn RayExcel VBA DeveloperCommented:
I posted that last answer via mobile... :-/  Here's the correct formula:
=DATE(YEAR(NOW()+IF(NOW()>DATE(YEAR(NOW()),10,5),1,0)),8,6)+60

-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.