Skip to main content
Question

I need dates instead of week numbers

  • March 5, 2019
  • 0 replies
  • 295 views
I am working on a resource planning app.  We need this done a weekly basis.  I currently created fields for week 1 - week 52.  This would work great if we used week numbers, but I really need dates, i.e., week ending date instead of week numbers.  This app will be used for multiple years, so I can't hard-code the dates.  Does anyone have a suggestion on how to make this work?  I am using if for capacity, demand, and the difference between the 2.  Thank you!
    This topic has been closed for replies.

    Well 51 of the 52 week dates are trivial because they are just X weeks after Week 1 date.

    What is the definition of the date for Week 1?  It's probably something like the first Sunday of the year or the first Monday of the year.

    • JuliePlummer
    • Author
    • Quickbase Alumni
    • March 5, 2019
    Week 1 is the week ending with the first Saturday of the year.
    Can you tell me the date you want to see for week 1 for 2019 and 2020 so I understand?
    • JuliePlummer
    • Author
    • Quickbase Alumni
    • March 5, 2019
    2019 would be 1/5/2019, 2020 would be 1/4/2020
    The formula for the first Saturday of the Year would be

    var date TodaysDate= Today(); // done this way so you can try with different dates

    var date FirstSaturdayOfYearRaw = FirstDayOfWeek(FirstDayOfYear($TodaysDate))-Days(1);

    If(Year($FirstSaturdayOfYearRaw) <> Year($TodaysDate), $FirstSaturdayOfYearRaw+Weeks(1), $FirstSaturdayOfYearRaw)

    The formula for the various dates for each week would be a separate field with the formula 

    [First Saturday of the Year] + Weeks([week number]-1)

    • JuliePlummer
    • Author
    • Quickbase Alumni
    • March 5, 2019
    Thank you Mark! That helps. Now I'm trying to figure out how I can show dates instead of the W1,W2... field names in reports.  Week numbers don't mean anything to the managers who will use the app.
    What is the data in the cells and what are your rows?  I had that exact same problem and was able to come up with a cheat to get the dates to show.  Are the rows your people (resources)? 


    • JuliePlummer
    • Author
    • Quickbase Alumni
    • March 5, 2019
    Yes, the rows are people.  The data in the fields are either the capacity hours, or demand hours.
    What are the columns headings now?  Are they just the week numbers?  is this a summary report type?

    If so than you can just make a new field to use as Columns.

    List(" ",
    PadLeft(totext(week number]),2,"0"),
    ToText(Week of Saturday))
    • JuliePlummer
    • Author
    • Quickbase Alumni
    • March 5, 2019
    The column headings now are week numbers.  I used summary fields in a table report, because I need to be able to color code when we have deficits.  I couldn't figure out how to do that in a summary report.
    OK, so its a table report, not a summary report.  What is the field type of the fields in the report.  Are they in fact formula rich text field type?  You said you were color coding, so they must be formula Rich Text - is that right?
    • JuliePlummer
    • Author
    • Quickbase Alumni
    • March 5, 2019
    Yes, I convert them to text, and use it in a rich text formula field
    I have the opposite issue, I need to convert the "Process Date" to show what week it falls in. First day of the year would be 12/30/2018, First day of the Week is Sunday...I'm new to QuickBase and struggling with Functions.
    Terms & ConditionsAccessibility statement