I need dates instead of week numbers

• 0
• 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!
• 80 Points

Posted 2 months ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
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)
• 80 Points
Week 1 is the week ending with the first Saturday of the year.

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
Can you tell me the date you want to see for week 1 for 2019 and 2020 so I understand?
• 80 Points
2019 would be 1/5/2019, 2020 would be 1/4/2020

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
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)

• 80 Points
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.

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
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)?

• 80 Points
Yes, the rows are people.  The data in the fields are either the capacity hours, or demand hours.

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
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(" ",
ToText(Week of Saturday))
• 80 Points
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.

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
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?
• 80 Points
Yes, I convert them to text, and use it in a rich text formula field
• 400 Points
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.
• 400 Points
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?
• 860 Points
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)