date calculation Certain Weeks Prior to A Date

• 0
• Question
• Updated 3 years ago

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.

• 3,152 Points Posted 3 years ago

• 0
• 70,354 Points You can use

[my date field] - Weeks(26)
• 3,152 Points Thanks a lot.  Would this be a week day calculation or do I need it to modify it?
• 70,354 Points 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

I have not tested that last part but t should work.
• 3,152 Points 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.
• 3,152 Points 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.
• 3,152 Points Sorry the  dates should be 06 March  2017 and 5 Sep 2016 using Access formula.
• 3,152 Points 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.
• 70,354 Points 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.
• 3,152 Points Thanks. but is this the right syntax with an OR to test for two conditions or is there a different convention.
• 70,354 Points Yes, that is correct syntax for OR. If you are getting a result of 18 weeks subtracted, then the OR condition must be false.
• 3,152 Points I tested on "N" project and still see the wrong result. Is there a 'Case' option I should try.