Round Up To A Specific Date...?

kellywy

Board Regular
Joined
Aug 5, 2006
Messages
123
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi Kellywy,

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

Excel Workbook
B
221/11/2010
232
243/11/2010
25
263/15/2010
Sheet7


Excel Workbook
B
221/17/2010
232
243/17/2010
25
264/15/2010
Sheet7


Welcome!
 

kellywy

Board Regular
Joined
Aug 5, 2006
Messages
123
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
 

kellywy

Board Regular
Joined
Aug 5, 2006
Messages
123

ADVERTISEMENT

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

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
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

Board Regular
Joined
Aug 5, 2006
Messages
123

ADVERTISEMENT

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

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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!
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 
Last edited:

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,912
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top