Filtering between first day and last day of month

[Jonathan1]

Hello Make Community,

I’m relatively new to using Make and I’m currently working on a project where I need to filter data in Google Sheets based on two criteria:

The age of individuals should be between 18 and 24 years old. (I do understand how to do)

The date should be within the current month, starting from the 1st of the month up to today’s date.

For example, if today is April 17, 2024, I want to filter and retrieve data from April 1, 2024, to April 17, 2024. This filter should be dynamic and adjust to the current date each month.

The date format in my Google Sheets is MM/D/YYYY.

I’m using the Google Sheets (Search Row)

[samliew]

Welcome to the Make community!

You can use the built-in functions setDate, setHour, setMinute, and setSecond to get the first day of this month.

For the last day of this month, it is a similar formula with two more built-in functions addMonths and addDays. This will automatically cater to months ending on different days (28/29/30/31) — which I have used here 📅 How to schedule scenario for the last day next month (recurring)

Output

For more information, see https://www.make.com/en/help/functions/date---time-functions

[samliew]

Module Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

1. Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
   

2. Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.

3. Click on each imported module and save it for validation. You might be prompted to remap some variables and connections.

JSON

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 1,
                    "module": "util:SetVariables",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "variables": [
                            {
                                "name": "FirstDayOfMonth",
                                "value": "{{setDate(setHour(setMinute(setSecond(now; 0); 0); 0); 1)}}"
                            },
                            {
                                "name": "LastDayOfMonth",
                                "value": "{{addDays(addMonths(setDate(setHour(setMinute(setSecond(now; 59); 59); 23); 1); 1); -1)}}"
                            }
                        ],
                        "scope": "roundtrip"
                    },
                    "metadata": {
                        "designer": {
                            "x": 0,
                            "y": 0,
                            "name": "First/Last Day of Current Month"
                        },
                        "restore": {
                            "expect": {
                                "variables": {
                                    "items": [
                                        null,
                                        null
                                    ]
                                },
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "variables",
                                "type": "array",
                                "label": "Variables",
                                "spec": [
                                    {
                                        "name": "name",
                                        "label": "Variable name",
                                        "type": "text",
                                        "required": true
                                    },
                                    {
                                        "name": "value",
                                        "label": "Variable value",
                                        "type": "any"
                                    }
                                ]
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            }
                        ],
                        "interface": [
                            {
                                "name": "FirstDayOfMonth",
                                "label": "FirstDayOfMonth",
                                "type": "any"
                            },
                            {
                                "name": "LastDayOfMonth",
                                "label": "LastDayOfMonth",
                                "type": "any"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}