My solution looks like this.
Two columns to calculate the numeric value of start datetime & end datetime.
YEAR(D)*10^8+MONTH(D)*10^6+DAY(D)*10^4+HOUR(D)*10^2+MINUTE(D)
A query to get all tasks from the same day.
A JS column to get the numerical values of start and end datetime, and return a list of minutes-level numeric values between those two datetime.
function getMinutesRange(startNum, endNum) {
// Helper to parse a number in YYYYMMDDhhmm format to a Date object in UTC
const parseNumberToDate = (num) => {
const str = num.toString();
if (str.length !== 12) {
throw new Error("Input numbers must be in the format YYYYMMDDhhmm (12 digits).");
}
const year = parseInt(str.slice(0, 4), 10);
const month = parseInt(str.slice(4, 6), 10) - 1; // Months are 0-indexed in JavaScript
const day = parseInt(str.slice(6, 8), 10);
const hours = parseInt(str.slice(8, 10), 10);
const minutes = parseInt(str.slice(10, 12), 10);
return new Date(Date.UTC(year, month, day, hours, minutes));
};
const start = parseNumberToDate(startNum);
const end = parseNumberToDate(endNum);
// Validate the parsed dates
if (start >= end) {
throw new Error("The start time must be before the end time.");
}
// Helper to format a Date object back to YYYYMMDDhhmm format
const formatDateToYYYYMMDDhhmm = (date) => {
const pad = (n) => n.toString().padStart(2, '0');
const year = date.getUTCFullYear();
const month = pad(date.getUTCMonth() + 1); // Months are 0-indexed
const day = pad(date.getUTCDate());
const hours = pad(date.getUTCHours());
const minutes = pad(date.getUTCMinutes());
return `${year}${month}${day}${hours}${minutes}`;
};
const results = [];
let current = new Date(start); // Create a copy to avoid mutating the original date
while (current < end) { // Change condition to exclude the last minute
results.push(parseInt(formatDateToYYYYMMDDhhmm(current), 10)); // Push as number
current.setUTCMinutes(current.getUTCMinutes() + 1); // Increment by one UTC minute
}
return results.join(", ");
}
return getMinutesRange(p1, p2)
Join the minutes of same-day tasks together.
Split them into an array.
Get unique elements from that array.
Count the elements in that array.
Bonus points: Convert it back to duration by having it as seconds first, then use the format duration column.
