If then else with DATE

Glide should release the pure date format of the user… it drives me crazy to set up apps…
it should be like Google is doing… just a number that you can convert any way you want!
I don’t have a problem with static dates… I always rely on google… but dynamic (now) dates… I have no choice since glide can’t do functions to get user device time… only to take my users to my website or a web app… this has to be fixed if glide wanna compete with big names there

Darren, here’s the background on why I did this. Conditional Rollups On Date & User. Maybe I didn’t explain my use case well earlier and would love to learn how to do this more efficiently. Your notes on how to do this have worked beautifully. Its just that I want the detail page to show ll prior periods and t+1 month so users can track their time and submit time entries.

Would it help if you used a math column instead of a format date column to get a numeric value such as 202203?

Something like this:
YEAR(Date)*100 + MONTH(Date)

Format Date is sketchy at best. The math column is much more reliable and this way you would end up with a number that you can use for your compare.

2 Likes

Is that two prior periods, or eleven prior periods? And presumably, prior to now?
How long is a period - one month?
And t+1 month means now + 1 month, or?

Never mind about the prior periods and revising slightly my prior explanation for t+1.

On March 31st I want it to open the calendar for April. So from March 1-30th it would not allow anyone to enter time for April. But on March 31st I want it to show a box for April which would allow time to be entered for April. Am also okay with opening the calendar on the first day of the month if that is easier. So open calendar for April on april 1st.

Hope this is making sense. Am attaching a screenshot of how this looks right now. I don’t want it to show May and June at the moment. But on April 30th or May 1st it needs to show the box for May otherwise folks will not be able to submit time entries for May.

Thanks much!

Okay, so it sounds to me like you just need a way to calculate the last day of the previous month (for each month), and compare that to the current date - yes?

Can you show me your underlying table structure?
I assume you have a table with 12 rows - one for each month, yes?
I am most interested in how you are storing the months, because that will be the starting point. Are they just strings (Jan/Feb/Mar…), or are you storing them as actual dates?

Here is the payment data table:


Thanks so much Darren for helping me with this.

You know, there is a really easy way to do this. It’s a bit of a brute force approach, but given that you’re already manually entering the year and month names for each row…

All you need to do is add a date/time column, and then manually enter the date of the first of each month for the corresponding row. Jan 1, Feb 1, Mar 1, etc.
Then add a math column that subtracts 1 (day) from that date/time column. This will give you the date of the last day of the previous month. You can then use that column as a filter in your inline list.

It’s not the most elegant solution, and you’ll have to continue to manually add new rows. But it looks like you’re already doing that anyway.

haha, actually if you’re doing it manually you could skip the math column and just directly enter the date of the last day of the previous month.

There is a much better way though.

Question: are the number of rows in this table going to grow indefinitely, or will you eventually limit to something like the past 1 or 2 years of data?

@Food2Soil - here, I think this is a better approach…

The math formulas used:

  • To add an arbitrary number of months to a date:
((Now-DAY(Now)+15)+(Years/12*365.25))
-
DAY((Now-DAY(Now)+15)+(Years/12*365.25))
+
DAY(Now)
  • To get midnight on the first day of the month from a given date:
Date+1-Day(Date)-Hour(Date)/24-Minute(Date)/1440-Second(Date)/86400
4 Likes

I suppose it gets tricky when the previous month’s day is higher than the next one’s possible…
For instance, if I simply need the next month same day but I don’t want to skip a month but stop at the last day if it’d be bigger than this does not seem to work for me:

I see. So your requirement is a bit more than simply adding one month. Just so I’m clear, is the following what you would expect?

  • 25th Jan → 25th Feb
  • 29th Jan → 28th Feb (non leap year)
  • 29th Jan → 29th Feb (leap year)
  • 30th Mar → 30th Apr
  • 31st Mar → 30th Apr
  • 1st Apr → 1st May

Yes this is correct… this would be the ideal result yes

Okay, so an easy way to do this would be to combine both approaches - that is, use the math formula to add 1 month in one column, and use the formula to calculate the last day of the following month in a second column. And then use a bit of if-then-else logic to decide which is “correct” by comparing the month numbers of each.

So something like this:

I suspect that @Jeff_Hager might be able to come up with a simpler approach :slight_smile:

2 Likes

@Darren_Murphy @Milan_Balogh Geez, I had to put on my thinking cap before my morning coffee. The same place that Darren got that original formula had a second formula. It looks like that old thread is unlisted now, so I won’t link to it, but the second formula dealt with adding years, and also accounted for leap year, so if you added 1 year to Feb 29th, then the next year if would calculated to Feb 28th instead of March 1st. Essentially it would figure out if the month of the future calculated date is different from the month of the source date, and if it was, then calculate the difference in days using that difference in months (it would always be 1 or 0).

This doesn’t work well if that formula modified to add months instead of whole years. (Unfortunately, I’ve probably suggested a handful of times for others to use that other formula, which was wrong when calculating months instead of years). The problem is amplified when February is involved, since the month offset might be one, but we actually want to offset by 2 or 3 days to get the last day in February.

So I had to rethink it a little bit and fix my formula. One thing I did was add a TRUNC to some of the math because I noticed when doing (Month/12*365.25), it would result in a decimal value that would offset the time. This usually isn’t noticeable, especially if you are only showing the date without a time, but this could be a problem if the source date time is close enough to the beginning or end of the day, causing the time to float to a different day and throwing off all of the math. So now I’m truncating that decimal so we only get the true number of days when converting months to days.

I’ve included an updated formula at the bottom of this post, with a bit of a breakdown below. Hopefully I can explain it, so it somewhat understandable what’s happening.

  • First it takes the source date, subtracts the number of days based on the DAY in that date, then adds 15. We are just trying a get a date roughly in the middle of the month, so when you add any number of months (converted to days), we’ll still end up somewhere in the middle of the resulting month. When converting months to days, it’s really just an average number of days out of 365.25 which is 30.4375 days per month. That’s why I’ve chosen to use approximately the middle of the month (-days+15) when doing this math. The days may float a little bit, but not enough to put us in the wrong month.
    ((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
  • Next, we do that same formula again, but extract the resulting DAY (that’s somewhere in the middle of the resulting month). We take that result and subtract those days from the calculated future date. This actually gives us the last day of the month prior to the calculated date.
    (result of above) - DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
  • Next we take the DAY number from the source date and add that to our calculated date. That way we end up with a future date that should have the same DAY number as the source date. These steps so far are the same as what @Darren_Murphy shared above, except I added the TRUNC.
    (result of above) + DAY(Date)

This gives us a date in the future that should have a matching day in most cases, but can be an issue if the source date has a DAY that does not exist in the month of the future date we are trying to calculate. For example…a source month having 31 days, but the resulting month only has 28, 29, or 30 days. The above formula would give us date that in the first 1, 2, or 3 days of the month AFTER the month we were trying to calculate. So because of that, we need to expand our math formula to now find the differential between the day of the date that was the result of our math, and the day of the source date. Then use that difference to subtract those days from the resulting date to get the last day of the month we actually wanted.

  • First we do the exact same math as above, but we only want to extract the DAY number from the resulting date. (Adding 1 month to Jan 31st gives us Mar 3rd, so we want to get the number ‘3’.
     DAY(((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
          -
          DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
          +
          DAY(Date))
  • Using that resulting day number of the calculated date, we then subtract the DAY number of the source date. For example, adding 1 month to Jan 31st, gives you March 3rd, so what we are doing is taking 3-31 to get -28.
    (Result from original formula) - Day(Date)
  • The MOD of -28 divided by 31 days leaves a remainder of 3 days that we can then use to subtract from the resulting date to get the last day of the previous month (the month we were trying to calculate all along).
     (Result from original formula)
     -
     MOD((Result from above),DAY(Date))

TLDR…
I know…it’s confusing, but it’s all about breaking it down into pieces and understanding each piece. Here is the full formula that should work as expected. Be sure to test it thoroughly. I only threw a few dates at it, but I felt pretty good about the results. @Darren_Murphy, this may not be simpler, but it’s less columns. :beers: :stuck_out_tongue_winking_eye:

((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
-
DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
+
DAY(Date)
-
MOD(
(DAY(((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
-
DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
+
DAY(Date))-DAY(Date))
,DAY(Date))

EDIT: I will add @Darren_Murphy that this formula does not calculate to midnight, or change the resulting time to anything other that whatever time was in the source date, unlike what you showed in your post. I’m assuming that dates are being worked with as a whole, so the time portion of a date should be irrelevant.

7 Likes

Jeff You Are Super Cool! :star_struck:

I imagine that if the Hyper-formula column is available this could be easy!

=EDATE(A5,1)

I agree! It’s a lot of work just to add a month, while keeping the day number consistent, or handling the edge cases with shorter months. From what I understand, typescript/javascript doesn’t have a native function to add a duration in months, so that might be why glide really doesn’t have a native function either. I think a lot of glide’s functionality is based on what’s available in javascript.

I suppose the end result can be interpreted different ways. Maybe adding a month could be interpreted as finding the same day number one month from now, or finding the same day of the week one month from now, or using an average number of days (30.4375) from now, or any other variation of interpreting what is considered a month.

What would be awesome is if we had an AddDur function, with 3 parameters. First the date, second the units (year/month/day/hour/minute/second), and third the number of units. Maybe there would need to be a fourth optional parameter to set the different types of ways to interpret a month. Something like this:
addDur(Date, 'M', 1, x)

Just thinking out loud…

6 Likes

mmm, I have a habit of doing that when I’m working with dates and I only care about the date part. It’s usually not necessary because I’ll wind up converting the date to a string or an integer before using it elsewhere. But I’ve just been tripped up so many times by that dangling time - and Glide makes that super easy because it’s often hidden from view - so it’s a bit of an extra security blanket :slight_smile:

1 Like

Yeah I guess it depends. I know IF conditions can be tricky with dates, since some of them only use the date and some include time. So I suppose it depends on which kinds of IF conditions you use.

1 Like

Thank you @Jeff_Hager , this works amazingly well, now I tested it in my application. And really appreciate the explanation too!

1 Like

I used this approach because everything else was too complicated with formulas etc. Long formulas unnerve me and since I just needed to compare two dates this method seemed simpler. Thanks for the great discussion on this.

1 Like