date calculation Certain Weeks Prior to A Date

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a data field and I would like to establish a date 26 work weeks prior to that. Not too sure if there is a week function that does this.  Effectively, the formula will look like this if I were to use MS access: 


If([DevCode] = "N" or [DevCode] = "RN", Dateadd["w", 26, Approved Completion Date], dateadd(["W", 25", Dept Signage Ordered])

Need to clear my concept on such calculations in QB. 

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
You can use

[my date field] - Weeks(26)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot.  Would this be a week day calculation or do I need it to modify it?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Can you give an example of the date and the result you want.  

This calculation would give the same day of the week as the original date.   So a Monday would become the Monday 26 weeks ago, and a Saturday would be 26th Saturday in the past.

If you wanted to move the result to the Monday, then you could do this

WeekDayAdd([My date field]- Weeks(26)),0)

I have not tested that last part but t should work.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Many thanks for your help.  I merely wanted to calculate 26 weeks in terms of 5 days a week calculation. Is your last solution is likely to provide that.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Mark, I don;t think I am getting the right result.  If I have a  date of  12 March  2017 and I want to find out the date 26 weeks back, that would be 5 Sep 2016 using Access formula. I am getting 6 Nov 2016 instead. This is after trying the first formula using week(x). So without being bogged down with work days, I need to get a comparable result.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Sorry the  dates should be 06 March  2017 and 5 Sep 2016 using Access formula.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
I might have found the reason for the discrepancy but I cannot explain it.  I am using the following statement.

If([DevCode] = "N" or [DevCode] = "RN",  [Approved Completion Date]-Weeks(26), [Approved Completion Date]-Weeks(18))

If I remove the if condition I get the right result but I do have to use the if condition. Am I making some mistake here?  Formula is otherwise acceptable to QB.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
I have not tried to count week my self on a calendar, but it appears that your formula is calculating 18 weeks in the past, and not 26 weeks. So that just means that your IF statement us not true, so it's using the "else" option.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks. but is this the right syntax with an OR to test for two conditions or is there a different convention.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Yes, that is correct syntax for OR. If you are getting a result of 18 weeks subtracted, then the OR condition must be false.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
I tested on "N" project and still see the wrong result. Is there a 'Case' option I should try.