Calendar issues

Ask for Help
1

I am a complete amateur, know nothing about coding. But I am trying to create some kind of app for registering visits to a cabin I am responsible for. The cabin is open to anyone who wants to use it. Everyone who visits the cabin registers in a book when they use the cabin. For the statistics, I have for many years registered each visit in a “manual” calendar and added up the number of visitors, overnight stays, number of visits per month and on which days of the week the cabin is used. In Glide, it is easy to create a system for me to record the number of visitors and when they have used the cabin (from arrival to departure). I currently have 2 problems. 1. How can I find out which days of the week they have been there? For example, if they arrive on a date and leave three days later. By using WEEKDAY I can easily get the day of arrival (eg a Thursday) and day of departure (eg a Sunday). How can I get Glide to calculate that there have also been visits on Friday and Saturday? Problem no. 2 concerns the calculation of the number of visitors per month. With the help of the forum, I have managed to get the date into a format (YYYYMM) which I can use to sum up the number of visitors per month. The problem occurs when someone has visited the cabin at the turn of the month, for example from 30 March to 3 April 2024. How can I then calculate that the first two days were in March and the last days were in April?

2

For this one, I think I’d probably use a JavaScript column. Provide the Start and End dates as inputs, then iterate through the date range, calculating the day of week for each date and pushing that into an array. Then return the array as a joined list of day names.

Again, I’d probably use JavaScript for this. But instead of returning a joined list, return a JSON string. Something like the following:

{
    "month": "April",
    "days": 3
},
{
    "month": "May",
    "days": 2
}

You could then use a joined list through a query or multiple relation to aggregate for multiple rows, and then extract data for specific months with a Query JSON column.

3

image
image1316×357 54.5 KB

Took Darren’s suggestion and made this for you.

Days Between:

// Parse the input dates
const start = new Date(p1);
const end = new Date(p2);

// Validate dates
if (start > end) {
    return('Start date must be before end date.');
}

let currentDate = new Date(start);
const days = [];

// Array to map getDay() results to day names
const dayNames = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];

// Iterate over each date in the range
while (currentDate <= end) {
    // Add every day to the array
    const dayOfWeek = currentDate.getDay();
    days.push(dayNames[dayOfWeek]);

    // Move to the next day
    currentDate.setDate(currentDate.getDate() + 1);
}

return days.join(", ");

Month Days JSON:

const start = new Date(p1);
const end = new Date(p2);

if (start > end) {
    throw new Error('Start date must be before end date.');
}

let currentDate = new Date(start);
const results = [];

// Helper function to format the month's name and year
const formatMonth = (date) => `${date.toLocaleString('default', { month: 'long' })} ${date.getFullYear()}`;

let currentMonth = formatMonth(currentDate);
let daysCount = 0;

// Iterate over each date in the range
while (currentDate <= end) {
    const month = formatMonth(currentDate);
    if (month === currentMonth) {
        daysCount++; // Increment count for the current month
    } else {
        // Month has changed, push the previous month and its count to results
        results.push({ month: currentMonth, days: daysCount });

        // Reset for the new month
        currentMonth = month;
        daysCount = 1; // Start counting the new month
    }

    // Move to the next day
    currentDate.setDate(currentDate.getDate() + 1);
}

// Don't forget to add the last month's data
results.push({ month: currentMonth, days: daysCount });

const jsonResult = JSON.stringify(results);
return jsonResult;

Please set start date as p1, end date as p2 in your JavaScript column.

1 Like
4

1 Like
5

Thank you so much you both! As I said I’m totaly amature, but I will try my best to implement this in my app. Very greatful for your help!

lør. 6. apr. 2024 kl. 03:45 skrev Darren Murphy via Glide Community <notifications@glideapps.discoursemail.com>:

6

you can calculate the duration of the visit and then use a loop to mark each day of the visit as attended. For the second problem, you can split the visit duration into segments by month, then count the days in each segment accordingly for accurate monthly visitor calculations.