Fun with Dates

2 Likes

I imagine it would be useful to calculate the next billing date in a monthly subscription. With PayHere, up until now, I do it in the Sheet.

For sure, though, technically, the next billing date is one of the API parameters.

2 Likes

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

1 Like

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!

2 Likes

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.

3 Likes

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

3 Likes

@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? :pray:

(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)

1 Like

I have finished :stuck_out_tongue_winking_eye: :stuck_out_tongue_winking_eye: :rofl: :joy: :smiley: :grinning:

4 Likes

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.

5 Likes

haha :joy:

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

3 Likes

Wonderful, thank you! :+1:

2 Likes

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:

CleanShot 2022-03-29 at 14.53.36

@Jeff_Hager genius.

5 Likes

@Robert_Petitto Brilliant!

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

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

1 Like

Infinite calendar using only 32 rows!

Uses increment > conditional template > set column action.

CleanShot 2022-04-01 at 01.17.10

2 Likes

@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.

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.

1 Like