Excel: Determining the Accounting Week of a Date

• 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).

REFERENCES

"Microsoft Excel Function Reference," for the Macintosh, version 3.0, pages 249

Additional query words: 2.2 2.20 3.0

Keywords          : 
Version           : 
Platform          : 
Issue type        : 

Last Reviewed: March 23, 1999