Forum Discussion

NateKurtz's avatar
NateKurtz
Qrew Trainee
5 years ago
Solved

Contract Year Formula

I am running into a wall trying to site a formula that kicks out the contract year.  So basically i want it to check the date of a job [Job Start Date] and tell me what year of the contract it is taki...
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    5 years ago
    I think that this works

    var date AdjustYearToCalendarYear = AdjustMonth([Job Start Date],-8)-Days(14);
    var date AnchorDate = Date(2017,1,1);
    Year($AdjustYearToCalendarYear) - Year($AnchorDate) +1


    The formula basically adjusts the job start date backward by 8 months and 14 days to normalize it to a normal January 1st year and then is able to just subtract the year from the Year of the Anchor Date.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
Are you trying to say that you have a fiscal year that starts in August?  What are the dates for your fiscal year.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
NateKurtz's avatar
NateKurtz
Qrew Trainee
5 years ago
This is what i have so far:

If(Year([Job Start Date])<=2018, If(Month([Job Start Date])<=9, If(Day([Job Start Date])<=14,1,
If(Year([Job Start Date])<=2019, If(Month([Job Start Date])<=9, If(Day([Job Start Date])<=14,2,
If(Year([Job Start Date])<=2020, If(Month([Job Start Date])<=9, If(Day([Job Start Date])<=14,3,
If(Year([Job Start Date])<=2021, If(Month([Job Start Date])<=9, If(Day([Job Start Date])<=14,4,
If(Year([Job Start Date])<=2022, If(Month([Job Start Date])<=9, If(Day([Job Start Date])<=14,5,
If(Year([Job Start Date])<=2023, If(Month([Job Start Date])<=9, If(Day([Job Start Date])<=14,6,
If(Year([Job Start Date])<=2024, If(Month([Job Start Date])<=9, If(Day([Job Start Date])<=14,7, 0)))))))))))))))))))))

but it doesn't seem  to be  working for me.

------------------------------
Nate Kurtz
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    .. tied up in EMPOWER today, so I will respond after EMPOWER.  I think that there is an elegant solution to adjust the date backwards to normalize to a normal year and then just subtract the Year from the current year.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • NateKurtz's avatar
      NateKurtz
      Qrew Trainee
      5 years ago
      Great!  Thank you for the quick responses!

      ------------------------------
      Nate Kurtz
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        I think that this works

        var date AdjustYearToCalendarYear = AdjustMonth([Job Start Date],-8)-Days(14);
        var date AnchorDate = Date(2017,1,1);
        Year($AdjustYearToCalendarYear) - Year($AnchorDate) +1


        The formula basically adjusts the job start date backward by 8 months and 14 days to normalize it to a normal January 1st year and then is able to just subtract the year from the Year of the Anchor Date.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------