I need dates instead of week numbers

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
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!
Photo of Julie

Julie

  • 80 Points 75 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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.

(Edited)
Photo of Julie

Julie

  • 80 Points 75 badge 2x thumb
Week 1 is the week ending with the first Saturday of the year.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Can you tell me the date you want to see for week 1 for 2019 and 2020 so I understand?
Photo of Julie

Julie

  • 80 Points 75 badge 2x thumb
2019 would be 1/5/2019, 2020 would be 1/4/2020
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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)

Photo of Julie

Julie

  • 80 Points 75 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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)? 


Photo of Julie

Julie

  • 80 Points 75 badge 2x thumb
Yes, the rows are people.  The data in the fields are either the capacity hours, or demand hours.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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))
Photo of Julie

Julie

  • 80 Points 75 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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?
Photo of Julie

Julie

  • 80 Points 75 badge 2x thumb
Yes, I convert them to text, and use it in a rich text formula field
Photo of Michelle Chapman

Michelle Chapman

  • 400 Points 250 badge 2x thumb
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.
Photo of Michelle Chapman

Michelle Chapman

  • 400 Points 250 badge 2x thumb
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?
Photo of Tyler Parker

Tyler Parker

  • 860 Points 500 badge 2x thumb
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)
(Edited)