airtable group holidays by month from a given period

---

@VallyS starbuckssecretmenu wrote:

Hello,

I've created a database with a form and applications that allow each employee to make holiday requests.
They can apply for leave over two months (example: from 10/08/24 to 16/09/24).
My requirement: I need to know, for each employee, how many days of holiday they have taken each month.

Additional information:
I have the date of the first day of leave + the date of the last day of leave + the total number of days of leave (excluding public holidays and weekends). If they only take half a day, the number of days off is 0.5.

What can I do?

Thank you for your help.

---

To track the number of holiday days taken by each employee per month based on their leave requests, you can follow these steps:

1. Data Structure
Make sure your database has the following structure for leave requests:

Employee ID: Unique identifier for each employee
Start Date: The first day of leave
End Date: The last day of leave
Total Days: Total number of days taken (including fractions for half days)
2. Calculate Monthly Breakdown
You’ll need to calculate how many days of leave fall within each month for the specified date range. Here’s a step-by-step guide:

Step 1: Extract Months
For each leave request:

Identify the months covered by the leave period (from Start Date to End Date).
You might need to split the total days across these months.
Step 2: Count Days per Month
For each month involved:

Count the number of days of leave that fall in that month.
This involves checking each date within the range and determining if it belongs to a particular month.
3. Pseudocode Example
Here’s some pseudocode to illustrate the logic:

from datetime import datetime, timedelta

def get_days_off_per_month(employee_id, start_date, end_date, total_days):
days_off = {}

# Initialize months dictionary
current_date = start_date
while current_date <= end_date:
month = current_date.strftime('%Y-%m') # 'YYYY-MM' format
if month not in days_off:
days_off[month] = 0
days_off[month] += 1 # Count each day as a day off
current_date += timedelta(days=1)

# Adjust for weekends and public holidays
# Assuming we have a function is_weekend() and is_public_holiday()
for month in days_off:
# Replace days off with adjusted values excluding weekends/public holidays
days_off[month] = adjust_for_weekends_and_holidays(month, days_off[month])

return days_off

def adjust_for_weekends_and_holidays(month, total_days):
# Logic to subtract weekends and public holidays
# Return the adjusted count
return total_days # Placeholder

# Example Usage
employee_id = 123
start_date = datetime(2024, 8, 10)
end_date = datetime(2024, 9, 16)
total_days = 10 # Example total days

days_off_per_month = get_days_off_per_month(employee_id, start_date, end_date, total_days)

4. Store Results
You can store the results in a table that keeps a monthly record of days off for each employee.

5. Reporting
Create a reporting feature that allows you to pull up this information for any given employee and time period.

Best regards,
JerryC

Try:
1. Create a formula field that checks whether the End Date is a different month from the Start Date called "Spans two months" or something and outputs "True" or "False"
2. Create a formula field that outputs the number of work days from the Start Date to the end of the Start Date's month
3. Create a formula field that will output the value of the field from step 2 if "Spans two months" is "True"

And you'd create the fields from step 2 and 3 for the End Date as well

After that, you'd need to have a new table where each record represented a single employee <> month combination, and you'd link the leave records to the appropriate Employee <> Month combination, e.g. for 10/08/24 to 16/09/24 for the employee Jerry, that leave record would be linked to "Jerry - August 2024" and "Jerry - September 2024"

You could then rollup the days as needed to get how many days of holiday they took each month