2 Replies Latest reply on Oct 25, 2016 5:01 AM by Leslie Raillon

# First day of the week of my past full week

Hello everyone,

I have been looking for a solution that I really cannot find.

I have a previous full week number formula that works fine:

Than I do:

Which gives me 14/02/1900 00:00:00

BUT I WANT: 16/10/2016

Really I am far...

I have understood why I think; which is because [WEEK NUMBER] is not for this years previous week at present.

Any tips?

Thanks a lot

Regards

• ###### 1. Re: First day of the week of my past full week

hi Leslie,

So the problem here is that your [Week Number] is the datepart of week...which is just the week number (say 44, or Week 44). When you then try and use this to create a date it only has the number 42! Dates are stored as numbers, which are then converted to the dates we (as humans) understand...starting from 01/01/1900....so 1st Jan 1900 is the number 1, 2nd Jan the number 2...and so on.

So if your [Week Number] was 44...the calculation you have says; convert the number 44 to a date...which would be 13/02/1900 (day 44 from 1900), and then add one to it (to give the 14/02/1900).

Something like

would give you a date from the previous week....you may need to substitute the -1, with a calculation on DATEPART('weekday',today()). This returns the weekday number (so Monday = 2, Tuesday = 3....) meaning you can adjust which date is returned depending on which day of the week it is (if for example, you always want to show the date as a weekending value).

Hope that helps and makes sense.

2 of 2 people found this helpful
• ###### 2. Re: First day of the week of my past full week

Hi

Thanks a lot!

This works great

Best regards

Leslie