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
Bookmarks