Fun with Dates

[ThinhDinh]

Cheers! Would also be helpful if you want to do a feature like “This day last month”

[ehdubya]

Hey @Jeff_Hager , @ThinhDinh or @Darren_Murphy - do any of you know how you’d calculate “week of”?

For example, the ideal display would look like this:

“Week of: June 14, 2021 - June 20, 2021”

Thanks for any and all help!

[Jeff_Hager]

I would think you could do the same thing @ThinhDinh did here, but change the formatting of the date math columns and add ‘Week of’ to the template.

Fun with Dates

I hope people would find this useful, I had a use case that needs users to submit a form once per week. Showing the week number to the admin might not be the most informative, so I used these to derive the start date and end date of each week (Monday and Sunday). [image] Start date: Idea was moving everything to the current Sunday, then move back 6 days. [image] End date: Same as above, but just move everything to the currrent Sunday. Weekday column was a simple weekday formula in a math…

[ehdubya]

Will try this out - thanks! Also - thanks too ThinhDinh!

[Darren_Murphy]

@Jeff_Hager or @ThinhDinh - having a mental block here…

I’m using the following to get midnight on the first day of the current month:

N+1-DAY(N)-HOUR(N)/24-MINUTE(N)/1440-SECOND(N)/86400

Which works fine, but what I need now is:

• Midnight on the first day of last month
• Midnight on the first day of 2 months ago
• Midnight on the first day of 3 months ago

Example: today is 27th August, I want 3 math columns that will return midnight on the 1st of May, June & July respectively. I’m thinking that the magic number 15 needs to come into play here, but struggling with it…

Help?

(I know I could do this easily enough with a bit of YC, but prefer not to resort to that unless there is no other option)

[Manu.n]

I have finished

[ThinhDinh]

Hi Darren.

I believe this will work.

X-DAY(X-DAY(X))

With X referencing the column just before’s timestamp.

I tested with some timestamps instead of Now just to make sure.

[Darren_Murphy]

haha

I could have also done it that way, but that’s not what I wanted

[Darren_Murphy]

Wonderful, thank you!

[Robert_Petitto]

@Jeff_Hager

or some other use cases.

Resurrecting this awesome thread. Found myself using this functionality a few times this past week. Here’s a way to switch between “today” and “last month” by writing the text value of last month to a date column. The date column then acts as filter to display the correct leaderboard:

@Jeff_Hager genius.

[Jeff_Hager]

@Robert_Petitto Brilliant!

[ThinhDinh]

Thansk for sharing! I’m surprised it works with set columns.

[Robert_Petitto]

It’s actually a choice component that’s writing that value, but ya, sure does!

[Robert_Petitto]

Infinite calendar using only 32 rows!

Uses increment > conditional template > set column action.

[rickc]

@Robert_Petitto I was going to post this in your other thread about creating a membership app but since you mention it here - I have found that the payhere api doesn’t always update the “next_charge_at” field. According to the api docs it only updates that field during subscription creation or cancelation, but not for recurring payments. Have you found this to be different? I just set up a test subscription that renews daily to fully test this.

Here are the payment success fields that are passed through: Webhooks · Developers

Here is an example of it not passing the next_charge_at field:

Reading this thread because my new thought is to set a next payment field myself by adding a month or year to the last payment date if the subscription is still active.

[ThinhDinh]

@rickc

I have found that the payhere api doesn’t always update the “next_charge_at” field.

@rickc

Reading this thread because my new thought is to set a next payment field myself by adding a month or year to the last payment date if the subscription is still active.

I can confirm this happened to me as well. From that point I have always used math columns to calculate it myself.

[rickc]

@ThinhDinh

I can confirm this happened to me as well. From that point I have always used math columns to calculate it myself.

Thanks for confirming! I realized I could easily do it with the addMonths argument in the make/integromat scenario too. I’ll post an update to that thread once I have it set.