Filter by 6 week duration, start of the week

[#1]

Hi -

I am interested in filtering data based on time stamp in data between now and 6 weeks, starting from first day of the week (Sunday). This requires additional logic when new year begins.

I looked for date time functions and noticed date increment function that would provide 6 weeks in to the future. How can I get start date that is 6 weeks into the past?

Thanks!

[#2 · Accepted solution]

Here is a little script that should give you a pointer towards solving your 6 week issue.

Names Default To Here( 1 );
// put in any date using the format of mm/dd/yyyy
inDate = "07/21/2021";
jmpDate = Informat( inDate, "mm/dd/yyyy" );
// Calculate the date 6 weeks before, and adjust to the
// Sunday prior to the absolute 6 week date
sixWeeksBefore = jmpDate - In Weeks( 6 ) - In Days( Day Of Week( jmpDate ) - 1 );
// Calculate the date 6 weeks after, and adjust to the
// Sunday following
sixWeeksAfter = jmpDate + In Weeks( 6 ) + In Days( 8 - Day Of Week( jmpDate ) );

Dialog(
	"Input date is: " || Long Date( jmpDate ),
	"6 weeks before is: " || Long Date( sixWeeksBefore ),
	"6 weeks after is: " || Long Date( sixWeeksAfter )
);

Look in the Scripting Index for help with the different functions.

[#3]

Appreciate your help, Txnelson. Thanks a bunch!

[#4]

Date Increment is in my opinion best way to go with this. It might be a bit difficult to understand first but after that it is fairly easy. To go back in time you can use negative increment.

Names Default To Here( 1 );
// put in any date using the format of mm/dd/yyyy
inDate = "07/21/2021";
jmpDate = Informat(inDate, "Locale Date");
Show(Date Increment(jmpDate, "week", 6, "start"));
Show(Date Increment(jmpDate, "week", -6, "start"));