Sales App - Follow-up Dates Filter

Ask for Help
1

Hi Team Glide,

I have created an app for sales. I have follow-up date column in spreadsheet.

I want to filter follow-up dates as following

Today
Tomorrow
Yesterday
Last 7 Days
Upcoming 7 days
Last 30 Days
Upcoming 30 Days
Custom Range

2

You have to do this using a nested IF function in the Google Sheets document.

3

I used Code > Hyperformula (Excel formula) for this directly in a Glide Data Editor Computed column.
So it’s always computed when I add new data rows from my app (I couldn’t have this behavior using formulas in my original Google Sheets).
For example in my case, I wanted to select dates for the last 14 days and the next 31 days (A1 is the given date you want to test, A2 is the number of days before now you would like to include, A3 is the number of days after now you would like to include):
=AND(A1-NOW()>-A2,A1-NOW()<A3)

image
image598×506 32 KB

4

This can be done with Glide date math and an if-then-else column, without the need to use a plugin.
Instead of using the number of days in a formula, you just need to determine the upper and lower bounds of the date range using a couple of math columns, eg:

  • A2 = Now - 14
  • A3 = Now + 31

Then the if-then-else column:

  • If A1 is before A2 then empty
  • If A1 is after A3 then empty
  • Else true
4 Likes
5

Thanks, it’s prettier in this way with 3 simple columns instead of 1 with a formula !
I couldn’t find a way to use the ‘today’ or ‘now’ value except in the specific field “DATE is before today” or “DATE is after today” in the if-then-else column, and it seems that the value can only be “today” and not “today -14”, so I turned it like this :

  • A1 = target date (Date & Time column)
  • A2 = A1 + 14 (Math Column)
  • A3 = A1 - 31 (Math Column)

And in the If-then-else Column

  • If A2 is before today then FALSE
  • If A3 is after today then FALSE
  • Else TRUE
1 Like