Discussions

Expand all | Collapse all

I need dates instead of week numbers

  • 1.  I need dates instead of week numbers

    Posted 03-04-2019 20:57
    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!


  • 2.  RE: I need dates instead of week numbers

    Posted 03-05-2019 00:43
    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.



  • 3.  RE: I need dates instead of week numbers

    Posted 03-05-2019 15:15
    Week 1 is the week ending with the first Saturday of the year.


  • 4.  RE: I need dates instead of week numbers

    Posted 03-05-2019 15:16
    Can you tell me the date you want to see for week 1 for 2019 and 2020 so I understand?


  • 5.  RE: I need dates instead of week numbers

    Posted 03-05-2019 15:24
    2019 would be 1/5/2019, 2020 would be 1/4/2020


  • 6.  RE: I need dates instead of week numbers

    Posted 03-05-2019 15:44
    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)



  • 7.  RE: I need dates instead of week numbers

    Posted 03-05-2019 15:50
    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.


  • 8.  RE: I need dates instead of week numbers

    Posted 03-05-2019 16:59
    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)? 




  • 9.  RE: I need dates instead of week numbers

    Posted 03-05-2019 17:19
    Yes, the rows are people.  The data in the fields are either the capacity hours, or demand hours.


  • 10.  RE: I need dates instead of week numbers

    Posted 03-05-2019 17:37
    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))


  • 11.  RE: I need dates instead of week numbers

    Posted 03-05-2019 17:46
    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.


  • 12.  RE: I need dates instead of week numbers

    Posted 03-05-2019 17:51
    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?


  • 13.  RE: I need dates instead of week numbers

    Posted 03-05-2019 17:54
    Yes, I convert them to text, and use it in a rich text formula field


  • 14.  RE: I need dates instead of week numbers

    Posted 03-07-2019 16:17
    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.


  • 15.  RE: I need dates instead of week numbers

    Posted 03-07-2019 17:14
    I could even use the same set up as last day of the week being Saturday so the first week ending of the year would be 1/5/19. How do I convert this from the "Process date" within a week to identify the week in my "Week" field?


  • 16.  RE: I need dates instead of week numbers

    Posted 03-11-2019 14:30
    It would be really long... but off the top of my head, if you already have a field named [Date] and make a formula-text field called [Week #] this should work for 2019, provided your desired output is "##-2019". The bad part is typing out all the options, but this should give you ideas.

    Case([Date], Date(2018,12,31),"01-2019", Date(2019,1,1),"01-2019", Date(2019,1,2),"01-2019", Date(2019,1,3),"01-2019", Date(2019,1,4),"01-2019", Date(2019,1,5),"01-2019", Date(2019,1,6),"01-2019", Date(2019,1,7),"02-2019", ..., ..., null)