Forum Discussion

BethanyHudson's avatar
BethanyHudson
Qrew Trainee
6 years ago

Converting Date field to Fiscal Year

I need a formula that will convert [Date] that I enter to show the fiscal year that it goes with. Our fiscal year is October 1 - September 30. I've already got my fiscal year starting in October within the app properties. This seems like it should be a simple formula, but I can't seem to figure it out.

------------------------------
Bethany Dickey
------------------------------
Formulas and functions

4 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Year([Date])+
    If(Month([Date]) >= 10,1,0)

    This assumes that your fiscal year for 2021 starts on this coming October 1st, 2020.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    [email protected]
    ------------------------------
    • BethanyHudson's avatar
      BethanyHudson
      Qrew Trainee
      6 years ago
      I get an error with the "Year([Date])+" portion of the formula: Expecting text but found number

      --

      Bethany Dickey
      Program Administrator
      Mobile Bay National Estuary Program
      118 N. Royal St., Suite 601
      Mobile, AL 36602
      251-459-8870






      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        The formula will return a number.  if you want a numeric result, then change the field type to Formula Numeric
        If you want to retain as a text result and a formula text field type , then convert the result to text like this


        ToText(
        Year([Date])+
        If(Month([Date]) >= 10,1,0))

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        [email protected]
        ------------------------------