Forum Discussion

AmberPolston's avatar
AmberPolston
Qrew Cadet
6 years ago

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.
Formulas and functions
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    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.
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    6 years ago
    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