Help

Create a condition with a Date field

Topic Labels: Dates & Timezones
Solved
Jump to Solution
1793 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Veronique_Blais
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

For each record, I have a start date (Date de parution) and an end date (Date 6 mois). They unique to every record.
I want to count how many we have sold of every record within those date (Sales are in the Ventes 2021 tab, which is already link to my working tab).
I cannot filter by a Date field, it seems. How can I work around this?

Capture d’écran, le 2022-07-13 à 13.25.38

Thank you

1 Solution

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

Hi Veronique, would the following work?

  1. We create rollup fields in Ventes 2021 of both Date de parution and Date 6 mois
  2. In Ventes 2021, we create a formula field that checks whether the record’s Date value is between the rolled up values of Date de parution and Date 6 mois from the previous stem
  3. In the Titres table, we modify Ventes 6 mois’s to conditionally filter on the formula field from step 2?

See Solution in Thread

4 Replies 4
TheTimeSavingCo
17 - Neptune
17 - Neptune

Hi Veronique, would the following work?

  1. We create rollup fields in Ventes 2021 of both Date de parution and Date 6 mois
  2. In Ventes 2021, we create a formula field that checks whether the record’s Date value is between the rolled up values of Date de parution and Date 6 mois from the previous stem
  3. In the Titres table, we modify Ventes 6 mois’s to conditionally filter on the formula field from step 2?
Veronique_Blais
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi @Adam_TheTimeSavingCo

What would be the formula for .2? I’m trying a SUMIF but it doesn’t seem to work.

Thank you so much!

Hi Veronique, I would use this formula:

IF(
  AND(
    Date <= {End Date (from Titres)},
    Date >= {Start Date (from Titres)}
  ),
  1
)

I’ve also set it up here for you to have a look at

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Screenshot 2022-07-15 at 2.06.35 PM
Screenshot 2022-07-15 at 2.06.30 PM

Veronique_Blais
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you so much @Adam_TheTimeSavingCo