Calculating 4th Sunday before a date with an expression

expression
piston
#1

1) Give a description of the problem

I’ve searched everywhere and attempted all I can think of on my own and cannot figure out how to calculate the date of the next Sunday after a specific date in an expression. I am trying to calculate the date of the 4th Sunday before the 25th of December (for this year it would be Nov 29th). In the snapshot, I have placed a {‘J’} in place of the expression that I am trying to calculate.

2) What is the expected behaviour?

The expression would output the date corresponding to the 4th Sunday before the 25th of December for the present {$year}. This year, for example, it should output Nov 29th, 2020.

3) What is happening/not happening?

I have tried converting a formula that works in Excel to match the WebCoRE expression syntax with no success.

=(int((A1-22)/7)7)+1 ==> addDays(int(addDays(‘Dec 25, {$year}’,-22)/(786400))(786400),1)

That expression evaluates to Jan 9th, 1970 8:36AM.

I understand that Excel doesn’t use Unix timestamps but they both use a fixed starting point so trying to calculate the amount of whole weeks since that starting point should still be usable.

I’ve tried other variations of that expression (calculating in milliseconds, changing formatting of the input date, etc).

4) Post a Green Snapshot of the pistonimage

download
download.png1178×787 139 KB

5) Attach logs after turning logging level to Full
N/A

#2

Here you go. I did a single expression and an intermediate variable to simplify the expression. Take your choice. Also, I did define a string DoW but you could just insert the string in the expression if you want. Either way, I believe this does what you want. I also tested the year 2022 when 12/25 falls on a Sunday and it still works. Enjoy!

Weeks
Weeks.png1178×469 87.3 KB

Produces this log:

5/5/2020, 11:06:27 PM +218ms
+132ms	║Sun, Nov 29 2020 @ 12:00:00 AM PST
+174ms	║Sun, Nov 29 2020 @ 12:00:00 AM PST
#3

Just for the fun of it, I created a generic version. This will find any specified day, any number of weeks before the start day. Using 12/25, Sun, and 4 weeks, the result matches above.

Weeks-Generic
Weeks-Generic.png1178×534 105 KB 

5/6/2020, 12:06:32 AM +25ms
+435ms	║For 12/25/2020, start day is on Fri and Sun 4 weeks earlier is Sun, Nov 29 2020 @ 12:00:00 AM PST

I tested with a loop and Tue and got this result:

5/6/2020, 12:10:02 AM +631ms
+415ms	║For 12/25/2020, start day is on Fri and Tue 4 weeks earlier is Tue, Dec 1 2020 @ 12:00:00 AM PST
+598ms	║For 12/25/2021, start day is on Sat and Tue 4 weeks earlier is Tue, Nov 30 2021 @ 12:00:00 AM PST
+867ms	║For 12/25/2022, start day is on Sun and Tue 4 weeks earlier is Tue, Nov 29 2022 @ 12:00:00 AM PST
+1053ms	║For 12/25/2023, start day is on Mon and Tue 4 weeks earlier is Tue, Nov 28 2023 @ 12:00:00 AM PST
+1239ms	║For 12/25/2024, start day is on Wed and Tue 4 weeks earlier is Tue, Dec 3 2024 @ 12:00:00 AM PST
+1423ms	║For 12/25/2025, start day is on Thu and Tue 4 weeks earlier is Tue, Dec 2 2025 @ 12:00:00 AM PST
+1608ms	║For 12/25/2026, start day is on Fri and Tue 4 weeks earlier is Tue, Dec 1 2026 @ 12:00:00 AM PST
+1792ms	║For 12/25/2027, start day is on Sat and Tue 4 weeks earlier is Tue, Nov 30 2027 @ 12:00:00 AM PST
+1978ms	║For 12/25/2028, start day is on Mon and Tue 4 weeks earlier is Tue, Nov 28 2028 @ 12:00:00 AM PST
+2161ms	║For 12/25/2029, start day is on Tue and Tue 4 weeks earlier is Tue, Nov 27 2029 @ 12:00:00 AM PST
+2344ms	║For 12/25/2030, start day is on Wed and Tue 4 weeks earlier is Tue, Dec 3 2030 @ 12:00:00 AM PST
#4

Nice! Wow. Thanks for the help! It works perfectly.

I wonder why expressions sometimes evaluate differently (or incorrectly) when you are typing them. I have always assumed that whatever it shows on top of the expression is what it would evaluate to if the piston at the moment when you type it.

For example, your “Set variable dayNumber” expression evaluates to 5:

02
Screenshot 2020-05-06 at 09.19.02.png1484×280 16.3 KB

When I copy paste it, the exact same expression on my piston evaluates to 0:

21
Screenshot 2020-05-06 at 09.19.21.png1476×288 16.4 KB

Regardless, it works very well once it is run. So it’s just a matter of one getting the idea that the expression is wrong when creating it since it is evaluating to some nonsensical result.

#5

When you define a new variable (like DoW) you have to save the piston and go back in for it to recognize the value you set. Until the piston is saved, the value is usually 0 or Null. Once you save and go in again to edit, the expression evaluation will usually be correct.

#6

To add a bit to @guxdude’s comments…

In an Expression box, a variable can be referenced once it is stored in memory.
(exceptions are system variables and $twcweather, which are pulled fresh)

There are cases where I have to save & execute a piston first, (to populate the variables). Then I can use the Expression editor to take it to the next level of programming.

#7

Got it. Now I know. Thanks for the help!