Skip to main content
Question

Formula to show which accounting period a specified date falls in

I have a procurement table which tracks our purchase orders and the date when the invoice  [ERP Approved Date] is entered into our accounting system for payment.  I run reports from this table each period to track accruals.  Does anyone know if there is a formula I could use to say which period the [ERP Approved Date] falls in?  We run on a 5, 4, 4 account period schedule.  For example, January is a 5 week period then February & March are 4 week periods; April is five weeks with May & June being 4 weeks.
    This topic has been closed for replies.

    Forum|alt.badge.img+14
    Amber, does the Period change year-to-year or is it always based on the Months regardless of the Year.  Depending on this, you could do a simple formula; or you might need a table.
    Laura Thacker | laura@intelligentdbs.com | (626) 771 0454
    Per the comment above - if you don't change them year over year (like in a leap year for example changing the values) - then you can do a simple Case() to determine the period schedule - and then calculate which part of the schedule it fell into.

    Case(Month([ERP Approved Date],
    1,5,
    2,4,
    3,4,
    .....)

    Chayce Duncan | Director of Strategic Solutions
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base
    Terms & ConditionsAccessibility statement