0

Formula to calculate the start date of a week

Jet Reports Historic Posts

February 05, 2013 15:39

None

Follow

How do I get the starting date of any week 2013 in JetReports?
For a report that gets all registered hours of employees for a given period of time, users can fill in the start and end weekno. in the format YYYYWW.
In the Options sheet I have these parameters:
Title Value Lookup Option Start YearWeek (YYYYWW) 201301 Option End YearWeek (YYYYWW) 201305
What formula do I use to get the starting date for week 201301? (the result should be 31/12/12)

Willem

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts February 13, 2013 11:02 Official comment

  Hughes,
  
  You made my day! :D :D
  Works like magic, and I'm saving this example for use in other weekly reports.
  I conclude that it is not possible in JetReports itself to perform this calculation? (which is now no longer a big problem)
  
  Thanks again!
  
  
  Willem

• 0
  
  Jet Reports Historic Posts February 05, 2013 15:56

  Hi,
  
  So this question is more about Excel date calculations than Jet. I created some Excel formulas that should work for you and I will attach them to this post. Does this work?
  
  Regards,
  Hughes

  • Week Calculation.xlsx
• 0
  
  Jet Reports Historic Posts February 07, 2013 10:32

  Hi Hughes,
  
  yes it is about Excel, but I thought I might get answers faster in this community then elsewhere ;) .
  And it worked :D now I'm goingto check if one of your formulas gives the result I need.
  
  thanks!
  
  Willem

• 0
  
  Jet Reports Historic Posts February 12, 2013 10:59

  Hmmmm the suggestions Hughes made do work, but are not quite the answer to my real problem. I need more flexibility, and don't want to have to change a lot of reports next year again……
  Let me explain some more….
  My company works with weekly periods, so I want to be able to select weeks rather than dates.
  The first day of week 01 of 2013 is monday 31/12/2012.
  What I am looking for is a formula that gives me the start date of week 01 of the year 2013.
  In the options sheet, users can enter startweek and endweek in the format YYYYWW (e.g. 201301).
  How do I get from the entry 201301 to 31/12/12?
  In JetReports there are possibilities for calculating dates, but I can't seem to get this one right. :oops:
  
  Very curious if someone has the solution to this one. :|
  
  Thanks in advance,
  
  Willem

• 0
  
  Jet Reports Historic Posts February 12, 2013 16:07

  Hi Willem,
  
  Oh it looks like I forgot one formula in my example. Just change cell D5 in the example to this: =VALUE(RIGHT(D3,2)) This will allow it to get the week number from the user entered value. Does this work for you now?
  
  Regards,
  Hughes