autocalculate current week of fiscal year?

Hi zb61,

Chip Pearson does a good job explaining the various 'week number' calculations that Excel has available. There are merits to each, but I'll let you read about them to decide for yourself.

http://www.cpearson.com/excel/weeknum.htm

Let us know if you need further information after reviewing his site.

Hi nvelasquez,

welcome to the forum. However ...

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

OK, so I'm not usually an advocate of reviving 2.5 year old threads, however, this thread is not marked "closed" so... I just have say to Teylyn, that to me Nelson's post was not only particularly relevant in this thread, but significantly relevant, in that, an answer to his question would very likely answer the OP's question, which hadn't been indicated as "solved" or "answered" yet. Nelson simply stated his reason for wanting to learn how to apply the SAME formula or type of formula, that the OP wanted to learn. His only questions was actually a question about the partial (IMHO) answer given by Daddylonglegs.

I noticed the formula that was posted (see below) for calculating the fiscal weeks starting with June 1st but I cannot figure out how to use that formula for another date that is not June 1st.
Posted formula:
So June 1st - June 7th is always week 1, June 8th - June 14th week 2 etc.?
If so then for a date in A1 [ A1 could contain a formula like =TODAY()] this will give the week number
=INT((A1-DATE(YEAR(A1+214),-6,-6))/7)
Can you please help? Thanks.

Everything else in his post was simply the context in which his particular application of this formula would be.

Although your title is "Forum Guru", I think "Overenthusiastic Forum Rules Enforcement Guru" would be more accurate. Instead of citing an irrelevant rule (in this case), why not try to answer the question...? Afterall, I'm pretty sure this forum was created to assist and provide legitimate help, not chastise someone everytime you think they violated a rule.

Nevertheless, I won't make this post all about that. I'll actually answer the question and provide a solution that works well, for those that may still be looking for something similar:

1) In Row 1, we will establish our column headers. The "A" column will be "Fiscal Start", "B" column will be "Today's Date", "C" column will be "Current Fiscal Week"
2) In A2, We will represent the first date of your fiscal year, whichever you company/organization may use. Enter the forumla =DATE(YYYY,MM,DD) where YYYY/MM/DD would have actual values, ie: =DATE(2012,01,01)
3) In B2, We will represent today's date; enter the formula =TODAY()
4) In C2 enter the formula =IF(ISBLANK(B2),"No Date to Evaluate",INT((DATE(YEAR(B2),MONTH(B2),DAY(B2))-$A$2)/7)+1) *Note, ensure this cell's/column's format type is set to General, or Number with 0 decimals.

5) optional: In case you want to see the what fiscal week another particular date falls on:
- In A3 type "Eval Different Date" (or whatever you prefer). In B3 enter the date you would like to check: ie: 8/18/2012, then starting at C2 copy/drag the columns down to C3. If you want to enter multiple dates, continue copying/dragging column C down however many rows you wish.

Attached is an example worksheet using 1/1/2012 as the start of the fiscal year, with the optional step 5 implemented, also added fiscal quarter, just look at the formula, you'll get the idea.

Side Note: Now, I read the rules, and I do not see anything in my post that I've done to violate any of them, but... I'm curious to see which rule(s) you (Teylyn) feel I'm breaking.

Hello, Jim.

First, whether or not you think our rules are irrelevant does not matter. The fact is that one user posted a question in the thread of another. Our rule specifically states that you're to start your own thread, and if another thread is relevant post a link to it.

Second, the original author posted another 35 times in this forum AFTER Daddylonglegs posted his solution. This leads me to believe that it resolved the author's problem, or the author found a solution elsewhere. There are thousands of threads that do not receive a "Thanks" or "That solved my problem" response from the author - which is somewhat rude in and of itself.

Finally, Teylyn is no longer a Moderator here, so barking up that tree trying to garner attention serves no purpose - other than to get my attention.

Enjoy your week of vacation, and be sure to read the forum rules when you get back as we're currently discussing changes to them. Thanks, and have a nice day.