Olá,
Posso colocar no mesmo calendário os feriados e os dias de férias de cada dos funcionários de uma fundação, como posso diferenciar os funcionários uns dos outros através de cores diferentes, por exemplo? é possivel criar uma formula que conte o número de férias de cada funcionário, de forma a saber quanto dias de férias já tiraram e quantos ainda têm por gozar dos 22 anuais a que têm direito?
Se for possível, alguém me pode dizer como?
Agradeço desde já a atenção dispensada.
You can do something like this. I’m grouping by the employee name, but you should group by the employee ID instead.
Then, to calculate vacation days. One question is when do they restart? Is it on 1st January?
If so, if I take a vacation from 24th Dec to 5th January of next year, would 1st - 5th Jan be counted towards next year?
Obrigada pela atenção dispensada.
Em relação à questão colocada, não é fácil, mas o ideal seria que os 5 dias de janeiro contassem para as férias do ano anterior, no caso de ainda não terem sido atingido os 22 a que têm direito, caso contrádio deviam ser descontados das férias do ano seguinte.
Em Portugal o trabalhador pode gozar as férias até 30 de Abril do ano seguinte, aquele em que deviam ser gozadas.
Would you only ever need to know each employee’s leave count left for the “current year”? Say do you ever need to know how many days I took off in 2023?
Sim, se possível.
It’s a bit complex, but here’s how I imagine it would work.
S*10^4 + 5*10^2 + 1 with S being the selected year. You would get 20240501 if you chose 2024. This is the “start date”.
(S+1)*10^4 + 4*10^2 + 30. You would get 20250430 if you chose 2024. This is the “end date”.
| User ID | Vacation | Start Date | End Date |
|---|---|---|---|
| U001 | Vacation to Bahamas | 2024-04-29 | 2024-05-05 |
| U002 | Vacation to Mexico | 2024-07-07 | 2024-07-14 |
| U002 | Sick day | 2025-03-05 | 2025-03-05 |
| U003 | Vacation to Vietnam | 2025-04-26 | 2025-05-10 |
Then, calculate “Start Filter” and “End Filter” with the formulas below:
“Start Filter”: MAX(YEAR(VS)*10^4+MONTH(VS)*10^2+DAY(VS),SF)
With VS being the vacation’s start date, SF being the “start filter” we already pre-calculated.
“End Filter”: MIN(YEAR(VE)*10^4+MONTH(VE)*10^2+DAY(VE),EF)
With VE being the vacation’s start date, EF being the “end filter” we already pre-calculated.
It looks like this at this point:
| User ID | Vacation | Start Date | End Date | Start Calculated | End Calculated |
|---|---|---|---|---|---|
| U001 | Vacation to Bahamas | 2024-04-29 | 2024-05-05 | 20240501 | 20240505 |
| U002 | Vacation to Mexico | 2024-07-07 | 2024-07-14 | 20240707 | 20240714 |
| U002 | Sick day | 2025-03-05 | 2025-03-05 | 20250305 | 20250305 |
| U003 | Vacation to Vietnam | 2025-04-26 | 2025-05-10 | 20250426 | 20250430 |
Finally, I have to use JavaScript here to calculate the difference in days between two numbers, excluding weekends.
function calculateDateDifference(startDateNum, endDateNum) {
const parseToUtcDate = (yyyymmdd) => {
const s = String(yyyymmdd);
const year = parseInt(s.substring(0, 4), 10);
const month = parseInt(s.substring(4, 6), 10) - 1;
const day = parseInt(s.substring(6, 8), 10);
return new Date(Date.UTC(year, month, day));
};
if (startDateNum === endDateNum) {
return 1;
}
let d1 = parseToUtcDate(startDateNum);
let d2 = parseToUtcDate(endDateNum);
let currentDate, targetDate;
if (d1.getTime() <= d2.getTime()) {
currentDate = new Date(d1.getTime()); // Clone
targetDate = d2;
} else {
currentDate = new Date(d2.getTime()); // Clone
targetDate = d1;
}
let weekdayCount = 0;
const oneDayInMs = 24 * 60 * 60 * 1000;
while (currentDate.getTime() <= targetDate.getTime()) {
const dayOfWeek = currentDate.getUTCDay(); // 0 (Sunday) to 6 (Saturday)
if (dayOfWeek !== 0 && dayOfWeek !== 6) { // If not Sunday or Saturday
weekdayCount++;
}
currentDate.setTime(currentDate.getTime() + oneDayInMs);
}
return weekdayCount;
}
return calculateDateDifference(p1, p2)
