Find the last day of the following month

Yeah, I’ve used quickcharts a few times. I like them a lot.

Just curious, would you have been able to accomplish the same thing in a more cumbersome way by incrementing days by adding or subtracting the increment value from a date, then converting dates to string dates without time using a template column, then using those dates for a relation? Doing it with ordinals is probably a lot easier, but I’m just trying to picture a way in my head without ordinals, because I probably would have approached it differently. Using ordinals does give me some new ideas though. Especially by taking that ordinal and adding (year*1000) to get a value like 2021024 for today’s ordinal number.

1 Like

You are a wizard :woman_mage: :star: :star: :star: :star: :star:

2 Likes

You beauty :+1:

1 Like

Mind blown :exploding_head:

1 Like

Yes, I tried a few different approaches before deciding that ordinals was the way to go.
I started off just trying to compare dates, but of course that didn’t work because NOW() is a constantly moving target. So then I started down the route of using templates to convert the date to a fixed string, but that got messy so I abandoned that idea. Then I decided that the best way to do direct comparisons was to first convert the dates into numbers. So I started with that by just doing Day(date), which worked but broke when I hit the month boundaries. Which finally led me to ordinals, and that’s where you came in :slightly_smiling_face:

The ordinals will break at the year boundaries, but…

You solved that problem for me as well :slightly_smiling_face:

2 Likes

May I ask for a little help please… based on this solution I’m trying to achieve
The same day one month later, and I used this (basically adding the days in my date to the end of this month:

((date-DAY(date)+15)+60)
-
DAY((date-DAY(date)+15)+60)
+
DAY(date)

And it kind of works except when my date is Jan 30 it will not respect February

Is there an easy way to simply get +1 month? Next week is super simple, but I couldn’t figure this out.

heh… I posted a video about 10 minutes ago that shows how to do this :slightly_smiling_face:

4 Likes

@Darren_Murphy @Jeff_Hager

I had missed this one. Wow, your solution Jeff is so elegant. I learnt so much from reading this thread. Thanks :pray:

1 Like

hehe, my 43 columns to Jeff’s one possibly not my finest hour :rofl:

Here’s Jeff “the column monster” dealing with all my superfluous columns…

3 Likes

Hey Jeff,

I am trying to figure out how to dynamically update a subscription period (No API).

The initial Start Date should be the date they sign up (that date is captured).
The initial End Date should be 30 days from the initial Start Date.

Next, I need the initial End Date +1 Day to become the new Start Date and find a new End Date 30 days from the new Start Date.

Ex:
Initial Start: 1/1/23
Initial End: 1/31/23
Next Start: 2/1/23
Next End: 3/2/23
Indefinitely

Is this possible? Also would be it be possible with not an excessive amount of Math Columns?

Thanks!

So to better understand…you’re not asking for 4 columns, right? You only want a start date and an end date that both automatically update every 30 days?

It could be 4 columns, but I would only be comparing the current start and end date columns in another table to determine if a user has reached their limit.

I capture survey results and compare if the date the survey was taken was during the User’s subscription period to know if it should be counted against their monthly limit.

I have both Free and Paid plans, where the Paid plans automatically have their subscription periods updated via API. However the Free Plan doesn’t get that luxury as all they need to do is create an account in my Glide App.

I just threw this together quickly based on how I currently understand your goal.

So basically it’s 3 columns. An initial start date, which will only be set once and never change, and then a calculated current Start Date and a calculated current End Date.
image

  • Initial Start Date is just a date column. Nothing special about it.
  • mth-Start Date is calculated based on the current date.
  • mth-End Date just adds 30 days to that calculated start date.
    image

Let me know if I’m misunderstanding anything.

Awesome!
And so using your example, when the mth-EndDate is reached, will the mth-Start Date change, causing the mth-End Date to also change.

Using your first row of Initial Start Date of 1/1/23, the mth-End Date is 1/31/23. Once it becomes say 2/1/23, will the mth-Start Date change to become 2/1/23, causing the mth-End Date to then change to 3/2/23?

Obviously haven’t done extensive testing on this, but that appears to be the case.

image

1 Like

Perfect! What took you a few minutes would have taken me forever. Thank you so much!

1 Like

Double check my math. 30 days from Dec 17th is Jan 16th, which also becomes the start of a new cycle. I think the start date is a day early, but I want to make sure you agree.

1 Like

If it is in fact a day early, then I think it’s just a small tweak to the formula. Be sure to throw some dates at it and test it to make sure you get the expected results.

1 Like

Thanks, I’ll test it out and tweak as needed!

1 Like