# Calculate week numbers from custom date

1. ## Calculate week numbers from custom date

Hi.

I want to calculate a week number from a date entered on a staff roster. The week numbering needs to start from the beginning of the Australian financial year (1 July) until the end of the financial year (30 June) in the following calendar year.

I could type the number in but was using the below formula to save a few key strokes.

The formula was:

=IF(ISBLANK(X3),"",INT((X3-DATE(YEAR(X3-31),7,1)-WEEKDAY(X3,2))/7)+2)

where I entered the date at X3 and the week begins on a Monday.

Formula worked fine from 1 July to end of 2010 but from beginning of 2011 the formula calculates the week numbers as negatives e.g. Week Ending: 13 March 2011 calculates as Week Number -15, Week Ending: 20 March 2011 as Week Number -14 etc.

Sorry but I struggle with anything too complicated in Excel so any help would be greatly appreciated.

Thanks
David

2. ## Re: Calculate week numbers from custom date

Hello,

this formula

=WEEKNUM(X3)+IF(MONTH(X3)<=6,26,-26)

delivers the same result as yours, except for week 1, which I guess is due to the fact that the start of week 1 is still in June. Would that be correct?

I'm sure DaddyLongLegs will swing by soon and deliver one of his date gems.

3. ## Re: Calculate week numbers from custom date

Thanks Teylen
I copied & pasted your formula but it calculated a date of 26/01/1900 not a week number.
What did I do wrong??
David

4. ## Re: Calculate week numbers from custom date

Format the cell as number, not date.

5. ## Re: Calculate week numbers from custom date

Thanks Paul. Reformating worked.

And thanks again Teylyn for the formula. It now works great. But as you pointed out, June 1 was on Monday and is technically in June of the previous financial year. My weeks are out by one e.g. the week ending 13 March 2011 is Week 37 according to my company, not Week 38 as calculated by the formula.

How would I correct that in the formula?

6. ## Re: Calculate week numbers from custom date

the week ending 13 March 2011 is Week 37 according to my company
I don't understand. In your initial question you say

I entered the date at X3 and the week begins on a Monday.
The formula I provided will deliver the result 37 for the week that starts with Monday 7-march 2011 (which is the week that ends on 13 March).

7. ## Re: Calculate week numbers from custom date

Assuming that the week numbers are defined a little like ISO week numbers, i.e. week 1 starts with the first Monday on or after 28th June then you could use this formula

=IF(X3="","",INT((X3-WEEKDAY(X3,2)-DATE(YEAR(X3+188-WEEKDAY(X3,2))-1,6,20))/7))

format result cell as general, works for any year.....

8. ## Re: Calculate week numbers from custom date

Teylyn
Thanks again for your help but it just doesn't seem to work for me. I can't see where we differ.
David

9. ## Re: Calculate week numbers from custom date

Your formula works brilliantly with every combination I have tried so far. You are the "date guru".
Really appreciate your help (and the help of all other posters).
Best wishes
David

10. ## Re: Calculate week numbers from custom date

Assuming that the week numbers are defined a little like ISO week numbers, i.e. week 1 starts with the first Monday on or after 28th June then you could use this formula

=IF(X3="","",INT((X3-WEEKDAY(X3,2)-DATE(YEAR(X3+188-WEEKDAY(X3,2))-1,6,20))/7))

format result cell as general, works for any year.....
Hi daddylonglegs, how would I modify this, in the case of the first day of the company year being the first Mon on or after 27th Feb 2012? Sorry, but I'm not very good with excel date logic. Would you be able to describe to me what the key parts of the formula are doing? I can understand that the first part is establishing what day of the week the date in X3 is, but after that, I'm stumped. I thought that the X3+188 might relate to the number of days after Jan 1st, the fiscal date (July 1st in your example) might be, in conjunction with the 6,20 being 20th June, but when I change these values, the formula breaks. Any help would be appreciated. Thanks

11. ## Re: Calculate week numbers from custom date

Hello raahl,

"hijacking" old questions is against the forum rules, can you please post this as a new thread, with a link back to here? Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1