Date Range - Count Days per Month

[yeoman99]

Hi,

I am seeking a formula to calculate the days per month assigned to each job. For example job 1 is 98 days and the formula automatically assigns the 98 days across the Prior Year (2017) and Jan-18 to Mar-18. Similar formula for each subsequent job based on the start and end dates of each job.

I've formatted the months to Jan-18 = 31/01/18 etc which I think helps with the calculation.

Description	Start	End	Days	Prior Year Days	Jan-18	Feb-18	Mar-18	Apr-18	May-18
Job 1	Fri 01-12-18	Thu 08-03-18	98	31	31	28	8
Job 2	Fri 09-03-18	Sat 17-03-18	9				9
Job 3	Sun 18-03-18	Thu 27-03-18	10				10
Job 4	Wed 28-03-18	Tue 29-05-18	63				4	30	29
Job 5	Wed 30-05-18	Mon 23-07-18	55						2

<tbody>
</tbody>

Thanks,

 

[Peter_SSs]

Welcome to the MrExcel board!

I have assumed you have a typo in your sample.
- For Job 1 you have the start date after the end date. I assume start date should be 01-12-17

See if this is any use.
I have used the first of each month as the dates in row 1.
Formulas in D2 & E2 copied down
Formula in F2 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Description	Start	End	Days	Prior Year Days	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
2	Job 1	1/12/2017	8/03/2018	98	31	31	28	8
3	Job 2	9/03/2018	17/03/2018	9				9
4	Job 3	18/03/2018	27/03/2018	10				10
5	Job 4	28/03/2018	29/05/2018	63				4	30	29
6	Job 5	30/05/2018	23/07/2018	55						2	30	23
Spread Days

 

[Ingolf]

You can use:

In D2 =C2-B2+1
In E2 =IF(SUM(F2:Q2)=D2,"",D2-SUM(F2:Q2))
In F2 =MAX(0,MIN($C2,EOMONTH(F$1,0))-MAX($B2,F$1)+1)
and if you want remove "0" (Zero) use Conditional Formating

 

[yeoman99]

Thanks for your input. I'm almost over the line with this. I changed E2 to =IF(SUM(F2:Q2)=D2,0,D2-SUM(F2:Q2)) so a zero is returned in Column E if applicable.

The final piece is the 2019 days. Job 8 has 4 days in 2019 but these are appearing in the "Prior Year Days" column E. These 4 days should be shown under the "2019 Days" column S. (I've manually typed them into Column S at present). Any thoughts? Thanks.

Description	Start	End	Days	Prior Year Days	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Total Days 2018	2019 Days	Total Days	Check
Job 1	01/12/2017	08/03/2018	98	31	31	28	8										67		98	0
Job 2	09/03/2018	17/03/2018	9	0			9										9		9	0
Job 3	18/03/2018	27/03/2018	10	0			10										10		10	0
Job 4	28/03/2018	29/05/2018	63	0			4	30	29								63		63	0
Job 5	30/05/2018	23/07/2018	55	0					2	30	23						55		55	0
Job 6	24/07/2018	06/09/2018	45	0							8	31	6				45		45	0
Job 7	07/09/2018	05/11/2018	60	0									24	31	5		60		60	0
Job 8	06/11/2018	04/01/2019	60	4											25	31	56	4	64	4
			400		31	28	31	30	31	30	31	31	30	31	30	31	365	4	404	4

<tbody>
</tbody>

 

[Peter_SSs]

Try these, copied down

E2: =MAX(F$1-B2,0)
S2: =D2-SUM(E2:Q2)

 

[yeoman99]

Try these, copied down

E2: =MAX(F$1-B2,0)
S2: =D2-SUM(E2:Q2)

Thanks Peter - all sorted now.

 

[Peter_SSs]

Thanks Peter - all sorted now.

Great, thanks for letting us know.