Forum Discussion

MichelleChapman's avatar
MichelleChapman
Qrew Assistant Captain
7 years ago

Formula to Generate Which week of the year it is based on date entered in ""Process Date"" Field.

In search of Formula to Generate Which week of the year it is based on date entered in "Process Date" Field. I tried one and it is telling me how many weeks it has bee since that week instead of the week it was. First day of the year would be 12/30/2019, and then 12/29/2020
Formulas and functions
Tables and fields
  • RyanStanford1's avatar
    RyanStanford1
    Qrew Captain
    7 years ago
    This may work for you, If you have a field which will have the start of the year, say "Year Start" then you can calculate how many days between Year Start and Process Date, divide by 7, to get the week.


    Int(ToDays([Process Date] - [Year Start])/7 +1)

  • MichelleChapman's avatar
    MichelleChapman
    Qrew Assistant Captain
    7 years ago
    I wouldn't necessarily have a field for "year start"...but I have seen formulas where you can define the first day of the year and build the formula off of that but that formula was giving me syntax errors so I couldn't use it and am not familiar enough with formulas to fix it.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    I can try to help you but would need to know what is the definition of the first day of the year?
  • MichelleChapman's avatar
    MichelleChapman
    Qrew Assistant Captain
    7 years ago
    The first day of the year would be 12/30/18 Sunday of the first week, which is week ending Saturday 1/5/19...it doesn't matter which date you go off of as long as it identifies that week as week 1.
  • MichelleChapman's avatar
    MichelleChapman
    Qrew Assistant Captain
    7 years ago
    I'm close with this function but this has the year starting on the literal Jan 1 2019-Tuesday when I need it to start Sunday 12/30/18 and the first week would end 1/5/19 
    Int(DayOfYear([Process Date])/7+1)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    This will work. 

    The formula Ending Saturday of First Week of Year is

    var date TodaysDate= [test today];

    LastDayOfWeek(FirstDayOfYear($TodaysDate))

    You would set up a field called test today to test various dates.  Then once you are satisfied, change the first formula variable to


    var date TodaysDate= Today();


    The there would be a separate field for the week number


    var date TodaysDate= [test today];

    var number WeeksSinceFirstWeek = 
    (ToDays(LastDayOfWeek($TodaysDate)-[Ending Saturday of First Week of Year]))/7;

    $WeeksSinceFirstWeek+1

  • MichelleChapman's avatar
    MichelleChapman
    Qrew Assistant Captain
    7 years ago
    I'm sorry I'm really new to QuickBase and formulas are one thing I really have a hard time understanding....would each formula above go in my weeks formula field or am I supposed to have the test today field still but rename to "FirstWeek" or something and put the first set of formulas there then the second set under the week formula? Then how do I incorporate the "Process Date" field as the field I am establishing the week for.... Or is the Process Date field supposed to be the "var date TodaysDate= [Process Date];"

  • MichelleChapman's avatar
    MichelleChapman
    Qrew Assistant Captain
    7 years ago
    That's what it looks like based on the other formula that doesn't have the right start date...not the new ones you are trying to help me understand...
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      The formula above will not give you are correct answer.  My formula will work, but I'm a QSP (Quick Base Solution Provider), so its hard to find the time to walk you though basic Quick Base formulas.  That is why you may need to books some time with me or else take some of the QuickBase University courses about formula building.

      If I find the time I can post a single formula to provide you with the Week number but I did the formula in a way that allowed for testing various dates so that is why there are three fields. One for the date to test, on for the first Saturday of the year and one for the Week number.
    • MichelleChapman's avatar
      MichelleChapman
      Qrew Assistant Captain
      7 years ago
      I really appreciate your time and assistance. I started at a new company on a trial basis to help with data...not expected to have ever used QuickBase before but to see how quickly I can adapt and create what they need. My apps are pretty in depth so far...I had found a way to build form rules that produced the correct weeks but that would mean manually entering every week of the year into the form rules page...every year. I know that the answer is formulas, in excel this function would be easy but it is much more complicated in QuickBooks. I have watched several videos but I am expected to be able to show visual results weekly as well and I am only in 3 days a week. The other days I am at another job. Once I get the information going I want to do the full QuickBase University course directive and try to achieve certifications, assuming they hire me full time. But I have to get there first. 
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Here is a simplified version using your process date

      Create a formula date field called 
       Ending Saturday of First Week of Year 

      LastDayOfWeek(FirstDayOfYear(Today()))


      Create a field for Week number

      var text 
      var number WeeksSinceFirstWeek = 
      (ToDays(LastDayOfWeek([Process date])-[Ending Saturday of First Week of Year]))/7;

      $WeeksSinceFirstWeek+1






  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    I think that all you need to do is to change the formula from

    LastDayOfWeek(FirstDayOfYear(Today()))

    to

    LastDayOfWeek(FirstDayOfYear([Process Date]))



  • MichelleChapman's avatar
    MichelleChapman
    Qrew Assistant Captain
    6 years ago
    That's what I tried but then 12/30/18 was coming up as week 53 instead of part of Week 1 in 2019.