If then else with DATE

Ask for Help
1

Hello, I am developing an APP for managing golf institutions and I have a question for managing variables according to the day. In golf there is a variable called “handicap index” that changes in my country, Argentina, every 14 days, and for golf tournaments I need to have the data previously, so far so good, the problem that arises is wanting to do an IF THEN ELSE so that IF (tournament day) is before the change date and get the value. But GLIDE won’t let me, I would have to do an IF THE ELSE day by day and if so, how many nested IF THEN can be had in GLIDE?

Thanks

1 Like
2

You’re correct. Glide can’t compare date values …yet. We’ve been waiting for a while now. Any progress @Mark?

3

Sorry, can’t give you a timeline.

4

I did it in the sheet to calculate the subscription expiration date

5

Hi, we don’t have exactly the same use case, but maybe this approach may help you (since I am totally rebuilding my app, I cannot share an illustrative link).

My Use case:
I have a list of standard recurring challenges among which kids will select some, then define a frequency (cf. your 14 days) to perform each challenge, then a starting date .
Each day while logging, the appropriate challenge has to be displayed according to this initial setup and Today() 's date.

My constraint:
Display is different from a user to another, so must be User Specific columns,ie. only dealt with in DataEditor.
But no Date calculations were possible…

Workaround 8 steps approach:
(1) In DataEditor: User Specific columns ‘Starting Date’ and ‘Frequency’,
(2) In DataEditor: template column on ‘Starting Date’ to reset the date format,
(3) In GSheet: a sheet with all 2020 dates and associated each day number of the year; idem with ‘Today()’ formula,
(4) In DataEditor: Relation to link the ‘Starting Date’ of the [challengeSelect] tab with the tab having the 2020 dates,
(5) In DataEditor: LookUp to bring back the day nb of the related dates (‘Starting Date’ & ‘Today’),
(6) In DataEditor: Maths to do the calculation (Today - startDate / frequency)
(7) In DataEditor: If-then-else displaying TRUE if the ‘mod’ of the division is 0 [calculation to be checked],
(8) In Glide layout: Filter on TRUE in the inline list component … !

Hope this helps

6

is there an update on this? Can we have Glide compare two dates and use a if-then-else?

1 Like
7

image

You mean like this?

8

I’m trying to compare the monthyear format from one column with a monthyear format in another column. I want to use if-then-else to do one thing if the monthyear in one column is greater than the monthyear in the second column. But right now the equalizer doesn’t show a ‘is greater or equal to’ option.

glidemonthcompare
glidemonthcompare1267×764 158 KB

9

Is MonthYear_Format using the Format Date plugin?
That’s going to give you a string, not a date. So it can’t be used for date comparisons.

What’s you end goal here?
Are you just trying to identify those rows where the corresponding year/month is before/after the current date?

Also, I noticed that you have one row in that table for each month, but then you are repeating those - presumably so you can cover multiple years?

What’s your expectation about how you will manage that over time? Will you just keep adding a new row for every month/year? Or do you plan to keep it to a fixed number of months?

There may be better ways to approach what you are trying to do…

1 Like
10

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

11

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.

12

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.

3 Likes
13

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?

14

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.

timeentry
timeentry1542×652 65.7 KB

Thanks much!

15

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?

16

Here is the payment data table:

paymentdatatable
paymentdatatable1530×667 172 KB

Thanks so much Darren for helping me with this.

17

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?

18

@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
5 Likes
19

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:

image
image621×593 38.2 KB

20

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