Excel Formula to plot working days in a range into relevant month

Question

Hi

I am looking for a formula to plot annual leave by month, a table below is an example. The first line shows 2 working days in January, the second line is annual leave that spans across 2 months with the correct number of working days allocated to each month (this is the part I am having trouble with). Bonus points if it could also work out line 3 which has half days thrown in the mix - I can live without this though - line 2 would be good enough.

Start Date	End Date	Jan 25	Feb 25
06/01/2025	07/01/2025	2
30/01/2025	05/02/2025	2	3
30/01/2025 PM	05/02/2025	1.5	3

Thanks guys!

Answer 1

Hi,

In cell C2, enter this formula

=LET(z,A2:A3,DROP(REDUCE("",SEQUENCE(ROWS(z)),LAMBDA(s,c,VSTACK(s,BYCOL(C1:D1,LAMBDA(a,IF(NETWORKDAYS(MAX(INDEX(z,c,1),a),MIN(EOMONTH(a,0),INDEX(B2:B3,c,1)))<0,"",NETWORKDAYS(MAX(INDEX(z,c,1),a),MIN(EOMONTH(a,0),INDEX(B2:B3,c,1))))))))),1))

This will automatically spill down and across - just ensure that those cells are blank.

Answer 2

Hello @Charlotte Bold,  

Good day! Thank you for posting your valuable question on Microsoft Q&A forum.   

To calculate annual leave by month, you can use the NETWORKDAYS function to count working days within each month. This works well for full-day leave. 

Step 1: Prepare your table 

Columns: Start Date, End Date, and one column for each month (e.g., 25-Jan, 26-Feb). 

For example: 

Step 2: Understand the logic 

• For each month, calculate the overlap between the leave period and that month. 
• Use:  
  • MAX(StartDate, MonthStart) > ensures start date is not before the month. 
  • MIN(EndDate, MonthEnd) > ensures end date is not after the month. 
  • NETWORKDAYS(Start, End) > counts working days (Mon–Fri). 

Step 3: Formula for each month: 

• For January (25-Jan column): 

=MAX(0, NETWORKDAYS(MAX(A2, DATE(2025,1,1)), MIN(B2, DATE(2025,1,31)))) 

• For February (26-Feb column): 

=MAX(0, NETWORKDAYS(MAX(A2, DATE(2025,2,1)), MIN(B2, DATE(2025,2,28)))) 

Step 4: Apply to other months and rows 

• Update the dates in the formula for each month (e.g., DATE(2025,1,1) and DATE(2025,1,31) > change to February, March, etc.). 
• Use AutoFill or drag down to apply the formula to other rows. 
• The result should look like this:  

If you want to include half-day leave, I recommend adding Start Half and End Half as helper columns: 

• Start Half = PM (means the first day is a half day in the afternoon) 
• End Half = AM (means the last day is a half day in the morning) 

This detail is important because Excel needs to know whether to subtract 0.5 from the first or last day of the leave period.   

• For January (E2), input:  

=MAX(0, NETWORKDAYS(MAX($A2, DATE(2025,1,1)), MIN($B2, DATE(2025,1,31)))) 

- IF(AND($C2="PM", MONTH($A2)=1), 0.5, 0) 

- IF(AND($D2="AM", MONTH($B2)=1), 0.5, 0) 

• For February (F2), just change the month in the formula:  

=MAX(0, NETWORKDAYS(MAX($A3, DATE(2025,2,1)), MIN($B3, DATE(2025,2,28)))) 

- IF(AND($C3="PM", MONTH($A3)=2), 0.5, 0) 

- IF(AND($D3="AM", MONTH($B3)=2), 0.5, 0) 

• Use AutoFill or drag down to apply the formula to other rows. 
• The result should be like this:  

I hope this helps! If you need further assistance or clarifications on any steps, please feel free to leave a comment. Thank you for reaching out! 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.