In nned of a formula that would keep my answer within the same year

[carhill32]

I have a particular situation in which I would like to create a formula that will resolve to a date within a given year that I am working within without leaving that same year.

For example, I need to create a formula that moves around within the current year 2013 WITHOUT moving my calculation to the following year of 2014.

When I attempt to create a calculation that calls for: October 2013 to 'add' three months to the formula, I would like that calculation to essentially provide me with a "wrap around" effect and provide me with the answer of "January 2013" (placed in column: "A1" on the spreadsheet) instead of the logical anwer of "January 2014". Normally, I could create a formula such as: EDATE (A1,-9) which would then give me: "January 2013", which is what I want. The problem with this formula is that not every month in 2013 needs to result to a "wrap around" effect due to the fact that it's potential answer will reside within the year 2013. I am just having problems creating this effect for the months of July going forward within 2013. Does this make sense??

I would like to create a function that would produce an asterisk "*" within year 2013 only, without adding my formula and moving my asterisk "*" to year 2014.

I have attempted to resolve using everything from: EDATE, DATE, IF & EDATE combinations with no resolution to my problem. It is my hope that I can handle this issue without using VBA due to the fact that other people that may use the spreadsheet are not too familiar with VB. I will use VB if I have to, it is just not my first choice and I would still need assistance with having this done in VB

Is there any pre-set formula that I can use that would resolve this issue? Thanks in advance.

[Pete_UK]

Try this approach:

=DATE(YEAR(A1),MOD(MONTH(A1)+3-1,12)+1,DAY(A1))

The bit in red is the number of months you want to add on. Instead of DAY(A1) you could just have the number 1 if you are only interested in the resulting month.

Hope this helps.

Pete

[carhill32]

Pete,

That completely worked!! You are awesome!!! Thank you, thank you, thank you!

[Pete_UK]

Well, that's good to hear - thanks for feeding back.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

EDIT: Hey!! that was my 6,000th post

[carhill32]

Congratulations to you and me then! It came at the right time for me!