Round Up To A Specific Date...?

[kellywy]

Hi! Haven't been around for awhile, but being the novice Excel-er that I am, I have run into another block wall. Here's my problem:

I need to know if there is a formula that will allow me to not only calculate a date from two different cells -- but also to round the answer to THE NEXT 15TH OF THE MONTH beyond that date.

Ex: Cell F5 has a purchase date. (1/1/10)
Cell F25 indicates a specific number of months. (3)
Cell F5 calculates the totals of F5 plus the # of mos. in F25. (4/1/10)
But what I need to do - is to have Cell F5 show 4/15/10. (or, if Cell F5 were to come out to be 4/16/10 due to different figures in F5 and F25 -- it would need to indicate 5/15/10, and so on.)

I hope that makes sense the way I explained it.....? If not - just ask questions and I'll try to clarify!

Thanks in advance for any help!!

--Kelly

 

[jgthtrotih]

Hi Kellywy,

i think i got what you want..let me know.

Excel Workbook
	B
22	1/11/2010
23	2
24	3/11/2010
25
26	3/15/2010
Sheet7

Excel Workbook
	B
22	1/17/2010
23	2
24	3/17/2010
25
26	4/15/2010
Sheet7

Welcome!

 

[kellywy]

That's it!!!! Woo-hoo! It never ceases to amaze me the power of this program and the people who are able to understand it (and help idgets like me with it)!

Thank you sooooooooooo much! And for such an incredible response, I'll even root for the Broncos next game. Appreciate your help!

--Kelly

 

[wsjackman]

also
=date(year(a1),Month(a1)+(day(a1)>15),15)

 

[kellywy]

Thank you! That's perfect, as well! I have a new problem I'm just writing up to post (that has to do with Annualized Rate of Return) -- any chance you could help me with that? If so, I could just post it on this thread......?

--Kelly

 

[wsjackman]

with a new subject it is best to start a new thread.
Many people do not look at a thread they previously looked at if they did not put in a response. Financial expertize is often very diferent than date expertize.

 

[kellywy]

Thank you so much (again!). I just posted it in a new thread. --Kelly

 

[jgthtrotih]

That's it!!!! Woo-hoo! It never ceases to amaze me the power of this program and the people who are able to understand it (and help idgets like me with it)!

Thank you sooooooooooo much! And for such an incredible response, I'll even root for the Broncos next game. Appreciate your help!

--Kelly

Glad i could help!..

forgot to ask..but what happens when the date is say "1/15/2009" does it stay the same or does it advanced to the next month?

{posted by wsjackman}

also
=date(year(a1),Month(a1)+(day(a1)>15),15)

nice formula much cleaner..you always come up with nice clean formulas!

Welcome!

 

[Scott Huish]

forgot to ask..but what happens when the date is say "1/15/2009" does it stay the same or does it advanced to the next month?

What happened when you tried it?
--------------------
That formula does have a problem though, it doesn't increment the months that you indicated. But what if you have 1/16/09 with a 2 month increment, should that round to 3/15/09 or 4/15/09?

 

[jgthtrotih]

What happened when you tried it?
--------------------
That formula does have a problem though, it doesn't increment the months that you indicated. But what if you have 1/16/09 with a 2 month increment, should that round to 3/15/09 or 4/15/09?

nothing happened when i tryed it stayed "1/15/2009"...i was asking the OP..for that information to change the formula accordingly..as for the rounding..lets see what the OP says.