Microsoft KB Archive/81028

{|
 * width="100%"|

Excel: Determining the Accounting Week of a Date

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

-

SUMMARY
The following formula returns the number of the accounting week for a date contained in cell A1 on a worksheet:

=(DAY(A1+4-WEEKDAY(A1))+MOD(WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1)),MONTH(A1+ 4-WEEKDAY(A1)),1))+2,7))/7

The following formula returns the number of the accounting month for the same date (1,2,3, and so on, for January, February, March, and so on)

=MONTH(A1-WEEKDAY(A1)+4)

MORE INFORMATION
Accounting weeks begin on Sunday and end on Saturday. All dates within an accounting week belong to the month of the Wednesday of that week. 2/1/91, for example, is a Friday and thus falls in the fifth accounting week of January (because the Wednesday of that business week falls on 1/30/91).