Automate Date Changes Within Excel Workbook

[Danno2cu]

Hello,
I am working in Excel using Microsoft Office Professional Plus 2010, accessed in a Citrix Network environment, running Windows Server 2008 R2 Enterprise, Service Pack 1 (no local drive & unable to install any software).

I have four workbooks that contain a variety of data and formula elements with each file representing one three month period or one quarter. Within each of these files I need to change all instances of a given date from the existing "Calendar Date" to my "Fiscal Date". In other words, the Calendar Year begins on Calendar Date 01/01/12 and my Fiscal Year begins on Fiscal Date 07/01/12. I need to locate any and all instances of 01/01/12 anywhere in the workbook file and replace it with 07/01/12, then locate any instance of 01/02/12 and replace it with 07/02/12, continuing like this for each date within the quarter (no Sunday dates are involved) changing each individual Calendar Date to the corresponding Fiscal Date for the entire quarter, and do this within each quarterly Workbook file.

The date values that I need to change are located on no less than eight (8) worksheets and in many different cell locations throughout the workbook. That is, these date values are not located in any consistent or otherwise relative cell locations and the date values are formatted in more than one date format style throughout the workbook, i.e. some instances are formatted as Monday, July 02, 2012 and other instances are formatted as 07/02/12.

(However, should it be necessary for implementation of any solution provided by the community, I can do a global format change making all date formats the same throughout the file and I can then manually restore to original date formats as needed after the actual date values have been changed).

Please let me know if you need additional information / clarification.

I thank you in advance for your attention to my query and for any solution suggestions and or module VBA code that you may offer.

Thanks,

Danno

[Rick Rothstein]

Two questions...

1) Are all the dates you want to change constants or could some of them be results from formulas?

2) Do you have any formulas (displaying dates or not) located anywhere within your workbook?

[Danno2cu]

Rick,
In response to your questions:
1) All of the date values within the files are constants that are not the result of a formula.
2) Yes there are formulas used throughout the files. There may be as many as 100 per worksheet and consist mostly of the following:

• =Sum (range:range)
• =LOOKUP(E21,$U$6:$U$83,$V$6:$V$83)
• =(N22+L23)-(M23)

Thanks for your quick reply,

Dan

[Rick Rothstein]

Okay, one more question. It looks like you are simply adding 6 months to each date within the workbook. What dates should August 29, 2013, August 30, 2013 and August 31, 2013 become?

[Danno2cu]

Rick,

In my original post I neglected to note that I only need to change dates up to and including the current date (2/18/2013).
Thanks,
Dan

[Rick Rothstein]

Rick,

In my original post I neglected to note that I only need to change dates up to and including the current date (2/18/2013).

I need you to answer the question I asked in Message #4... it will tell me how to handle dates that do not have a corresponding day number in month that is 6 months hence.

[Danno2cu]

The answer to your question: What dates should August 29, 2013, August 30, 2013 and August 31, 2013 become? is those dates would also become a date 6 months into the future.

Thanks,
Dan

[Rick Rothstein]

The answer to your question: What dates should August 29, 2013, August 30, 2013 and August 31, 2013 become? is those dates would also become a date 6 months into the future.

You completely missed the point of my question. Six months from August 29, 2013 should be February 29, 2014... six months from August 30, 2013 should be February 30, 2014... six months from August 31, 2013 should be February 31, 2014; however, there are no such dates as February 29, February 30 and February 31 in the year 2014... so I need you to tell me what dates you want them to become... should they all become February 28, 2014 (the last day of the month that is six months hence) or should they "leak" over into the next month and become become March 1, March 2 and March 3 respectively?

[Danno2cu]

Rick,

My apologies for not being more clear in my original post.

I'm looking for a way to convert dates in Excel from a current date to a different yet correspondingly valid "day-of-the-week" date in the future.

In the example I used regarding Calendar vs. Fiscal dates, it was my intention to illustrate that if the Calendar year begins on Sunday, January 1, 2012 and my Fiscal year begins on Sunday July 1, 2012 how do I convert all instances of Sunday, January 1, 2012 to Sunday July 1, 2012.

Unfortunately for purposes of clearly describing my intention, these two months both begin with the first of the month being on a Monday. To be more descriptive, my example should have been something more like: How do I locate all instances of Calendar Date Monday March 5, 2012 and convert it to Fiscal Date Monday June 4, 2012 and then Tuesday March 6, 2012 and convert it to Tuesday June 5, 2012?

Does this help clarify?

Dan

[Rick Rothstein]

To be more descriptive, my example should have been something more like: How do I locate all instances of Calendar Date Monday March 5, 2012 and convert it to Fiscal Date Monday June 4, 2012 and then Tuesday March 6, 2012 and convert it to Tuesday June 5, 2012?

Does this help clarify?

That clears up one misunderstanding I had, but unfortunately raises another. Why is the March dates being advanced to June? My first impression is that all dates would be advance by six months. Then, in re-reading your original message coupled with your last message, I could not decide if the "add 6 months" was a blanket rule for any date or if you wanted all dates advanced to July of that year, but moving from March to June does not fit in with either of those "guesses" by me. Maybe you could post a table with one or two dates from each month of the year and show me the dates they should become... then maybe I will be able to see the underlying logic for your request, but right now, I seem to be completely missing it.