Finding date in a Range

[MattLB]

@Robert_Petitto - saw your latest video and thought I would walk through how I ‘solved’ this issue. I was in high-tech ‘Sales’ for 20+ years and lived my life by quarters. My leadership liked roll-ups of weekly activity, monthly attainment, quarterly goals/numbers plus keeping MTD, QTD and YTD updated.

This ranges are everywhere in the APP I am attempting to build so I needed a way to consistently and without pain have this functionality. Here we go:

I created a table in google with an array column that looks like this and took 5 minutes to build letting sheets do all the work. The most work was splitting the weeks in any given month (e.g. week 6 is in both Jan and Feb).

Now I let Glide do all the work. I create a relation on a date (if it is a Date-only column it works, if it is a Date-Time column(like a meeting) I convert it using the Date Compute Column):

It may be inefficient but Glide handles the rest - I just create a relation between “Create Date” and my table and I get the Week/Month/Quarter/Year via ‘Look Up’ from the relationship.

The best part is I can use this technique (slightly modified) for companies with different fiscal quarters (NetApp’s Q4 is Feb/Mar/Apr : Dell’s is Oct/Nov/Dec, Oracle is Mar/Apr/May). I can set up Company specific ‘fiscal quarters’ with different sheets but use the same technique for finding TW/TM/TQ/TY dates to filter/roll-up on.

I then use templates/ITE columns to create ‘search filters’ so I can get all meetings set This Week/This Month / This Quarter and YTD.

So far so good - this technique has been transportable to other ‘date-related’ items like Events and Tasks.

[ThinhDinh]

@MattLB

Now I let Glide do all the work. I create a relation on a date (if it is a Date-only column it works, if it is a Date-Time column(like a meeting) I convert it using the Date Compute Column)

Gotta tell you that this is not reliable and might break with iOS. Nowadays I stay away from this, it caused me too much trouble in the past. Glide’s underlying date/time format combined with Luxon was not friendly for Safari, and it almost always returned “invalid” for me.

I would just convert it to a numerical column, I believe Bob had the same method in his video. I usually do YYYYMMDD so my formula is:

YEAR(D)*10000 + MONTH(D) * 100 + DAY(D)

With D being the timestamp in question.

[Robert_Petitto]

Yup. Dates are not reliable…numbers are.

[ThinhDinh]

Seeing that you have a list of days in a week though, another approach (which I would like to have inputs from @Robert_Petitto @Darren_Murphy @Jeff_Hager and others):

• Use a fixed date in the past as a point of time to calculate a difference of days. Let’s say 1st January 1970.

• Add it as a date/time value in a separate sheet, cast it to your User Profiles sheet using a single value column to make it reference-able everywhere.

• For your week, you just need the start date and the end date. Calculate the difference of days for the start date (start date minus 1st Jan 1970), same for end date.

• Use a JavaScript column to return a list of numbers ranging from the start date difference to the end date difference. This is so that it is a consecutive list of numbers (not the case when you have weeks hanging between two months and you use the YYYYMMDD approach).

• Split that list to an array and you can do relations pretty easily with that.

[gvalero]

Hola @MattLb

This thread can help you to understand and have a right solution

Formatting dates to create relations

Greetings, I am trying to use dates to schedule pick up and drop off times for an event. I am having the toughest time creating relations. I am wondering if the format of the date makes any difference, meaning: When I input dates by hand into sheets and click on them to expand them, they look like this: [2022-05-14 (1)] However, when the user enters it from the date picker, it looks like this: [2022-05-14 (2)] Might that affect my ability to create a relation between them? I ultimately woul…

Saludos!

[MattLB]

I can change dates to numbers - the critical aspect is getting changing a date into (Week/Month/Quarter) taking into consideration that months split weeks as do quarters.

I can replace dates with numbers (I had to do the conversion in the first place because some of my data is date (which worked) and some is date/time (which dod not work hence the conversion)) but the end goal is transforming a date into a reportable ‘key’.

Since Glide can not filter I have to construct filters to allow for Week/Month/Quarter reporting using Templates and ITE to resolve to a ‘matchable’ column.

I am hoping/praying multi-filters (and date ranges) are coming.

The last item I am working on is hierarchical roll-ups. Rolling up as follows: Geo (e.g. US) / Area (e.g. West) / District (eg Southwest)/ Field Sales (Los Angeles). I have to put everyone one of these items in every record so I can ‘roll-up’ the appropriate data set. It is very hard-coded but I don’t know a ‘general’ means of representing hierarchy in Glide (its tough enough in a RDMS) and searched and didn’t see this in the community. I feel my way is a kludge - but I am at where I am at.

Thanks for any insight

[MattLB]

Thanks! I saw those and the way I convert dates works but the experts believe it is less reliable moving into the future. Right now I create a relation with any date and through a relations I get the WEEK/MONTH/QUARTER that date falls into and it works.

After I get the first release out (I have only shown it to 2 companies and both asked for more reporting) I can go back/hire someone to make it more bulletproof.

I really appreciate the feedback though, the Glide community is pretty awesome.

[Robert_Petitto]

@MattLB

the Glide community is pretty awesome.

Completely agree!