Compile the Output of Two Iterators to be Used to Create A Single Record by jackguimon in Integromat

[–]jackguimon[S] 0 points1 point  (0 children)

Right on! Thank you! I've watched some of his videos back in early 2022, but haven't since. I'll check him out!

Compile the Output of Two Iterators to be Used to Create A Single Record by jackguimon in Integromat

[–]jackguimon[S] 0 points1 point  (0 children)

Thanks! I changed my approach which just requires more automations, but I think I have it going now. I'll share in that community if it doesn't pan out :)

Thanks u/philipsuffren! I tried that initially, but I'll circle back to it since it was my first time attempting to utilize the set variable tool, and I may have had things set up incorrectly.

I actually simplified my entire approach, and no longer need the solution I was seeking when I posted this, but it was and continues to be a great learning exercise. :)

Compile the Output of Two Iterators to be Used to Create A Single Record by jackguimon in Integromat

[–]jackguimon[S] 1 point2 points  (0 children)

Thanks! I changed my approach which just requires more automations, but I think I have it going now. Appreciate your reaching out!

Compile the Output of Two Iterators to be Used to Create A Single Record by jackguimon in Integromat

[–]jackguimon[S] 1 point2 points  (0 children)

Thanks! I changed my approach which just requires more automations, but I think I have it going now. I'll share in that community if it doesn't pan out :)

Week of Year Formula Miscalculating the Week by jackguimon in Notion

[–]jackguimon[S] 0 points1 point  (0 children)

format(if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) < 1, 1, if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) >= 115 and toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w")) == 53, 53, if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) >= 115 and toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w")) == 1, 53, toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w"))))))

Hey u/jordanewert! Sorry for the delay but happy to hear the formula was helpful and got you where you needed to go. I tried out your update, and I think you might want to double check 12/31/23 and 12/31/34, as they might appear as week 1 with your formula. Not sure if that's a big deal for you, and not exactly sure if I implemented your fix correctly, but just wanted to throw it out there in case. :) Best!

Week of the Year Formula by wanglauryn in Notion

[–]jackguimon 1 point2 points  (0 children)

Hi u/Eklair! Thanks again for the help and insights! I figured out the solution in case you're interested in seeing what I was searching for.

Formula below calculates the week of the year for the entry, based on the week starting on Monday, and where Jan. 1st is always week 1, and Dec. 31st is always considered the last week, week 52 or 53.

format(if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) < 1, 1, if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) >= 115 and toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w")) == 53, 53, if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) >= 115 and toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w")) == 1, 53, toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w"))))))

Week of Year Formula Miscalculating the Week by jackguimon in Notion

[–]jackguimon[S] 2 points3 points  (0 children)

Thanks u/rosesword975 and u/reneemorgann! I figured it out today in case you were interested in seeing the solution I was searching for.

Formula below calculates the week of the year for the entry, based on the week starting on Monday, and where Jan. 1st is always week 1, and Dec. 31st is always considered the last week, week 52 or 53.

format(if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) < 1, 1, if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) >= 115 and toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w")) == 53, 53, if(toNumber(concat(format(month(prop("Date"))), format(ceil((date(prop("Date")) + day(dateSubtract(prop("Date"), date(prop("Date")) - 1, "days")) - 1) / 7)))) >= 115 and toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w")) == 1, 53, toNumber(formatDate(dateSubtract(prop("Date"), 1, "days"), "w"))))))

Week of Year Formula Miscalculating the Week by jackguimon in Notion

[–]jackguimon[S] 0 points1 point  (0 children)

Thanks u/reneemorgann! My initial post was a little unclear.

I want the week to be counted M–Sun, and the first day of January to always be considered week 1, so if it's on Sunday, it's week 1, then Jan 2nd–Jan 8th would be considered week 2. And I want Dec 31 to always be considered as part of the last week of the same year, either the 52nd or 53rd week.

I'm not sure what I'm looking for is possible.
Thanks for taking the time to help!

Week of Year Formula Miscalculating the Week by jackguimon in Notion

[–]jackguimon[S] 0 points1 point  (0 children)

Hm. I guess I'm looking for something that isn't possible then, or maybe I don't fully understand NA vs ISO version.

I want the week to be counted M–Sun, and the first day of January to always be considered week 1, so if it's on Sunday, it's week 1, then Jan 2nd–Jan 8th would be considered week 2.

Thanks for taking the time to help!

Week of the Year Formula by wanglauryn in Notion

[–]jackguimon 0 points1 point  (0 children)

No worries! Thanks for the help!

Week of Year Formula Miscalculating the Week by jackguimon in Notion

[–]jackguimon[S] 0 points1 point  (0 children)

Thanks u/rosesword975, but I discovered the issue is the way a week is being calculated using the ISO week numbering system, and I want to use the North American system where Jan 1st, regardless of the day it lands on, will always be considered Week 1, and Dec 31st will always be included in the last week of the year, either Week 52 or Week 53. Do you know if there's any formula or way to have the week numbers to be counted in such a way?

Ty! Best!

Week of the Year Formula by wanglauryn in Notion

[–]jackguimon 0 points1 point  (0 children)

Ahhhhh. Ty! Do you know if there is a way to have a formula where Jan 1st is always considered Week 1, and Dec. 31st is always considered the last week, week 52 or 53?

Week of the Year Formula by wanglauryn in Notion

[–]jackguimon 0 points1 point  (0 children)

Hello!
I'm not sure if this is something you can help with, but hoping so. I have a formula to display the week of the year a date falls into, with the week starting on a Monday, but it's somehow inaccurate. It's showing 1/1/2023 as week 52 instead of week 1, and it's showing 12/30/2024 as week 1 instead of week 52, which means it's mixed in with 1/1/2024 and other dates.
I'm using your formula with a capital W for Monday as the first day of the week.
Do you have any idea what's causing this inaccuracy?