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.
[TABLE="width: 551"]
<tbody>[TR]
[TD="class: xl66, width: 78, bgcolor: transparent"]Description
[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]Start
[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]End
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Days
[/TD]
[TD="class: xl69, width: 68, bgcolor: transparent"]Prior Year Days
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Jan-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Feb-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Mar-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Apr-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]May-18
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 1
[/TD]
[TD="class: xl73, bgcolor: transparent"]Fri 01-12-18
[/TD]
[TD="class: xl74, bgcolor: transparent"]Thu 08-03-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]98
[/TD]
[TD="class: xl75, bgcolor: transparent"]31
[/TD]
[TD="class: xl75, bgcolor: transparent"]31
[/TD]
[TD="class: xl75, bgcolor: transparent"]28
[/TD]
[TD="class: xl75, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 2
[/TD]
[TD="class: xl73, bgcolor: transparent"]Fri 09-03-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Sat 17-03-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]9
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]9
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 3
[/TD]
[TD="class: xl73, bgcolor: transparent"]Sun 18-03-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Thu 27-03-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]10
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]10
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 4
[/TD]
[TD="class: xl73, bgcolor: transparent"]Wed 28-03-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Tue 29-05-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]63
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, bgcolor: transparent"]30
[/TD]
[TD="class: xl75, bgcolor: transparent"]29
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 5
[/TD]
[TD="class: xl73, bgcolor: transparent"]Wed 30-05-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Mon 23-07-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]55
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]2
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks,
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.
[TABLE="width: 551"]
<tbody>[TR]
[TD="class: xl66, width: 78, bgcolor: transparent"]Description
[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]Start
[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]End
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Days
[/TD]
[TD="class: xl69, width: 68, bgcolor: transparent"]Prior Year Days
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Jan-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Feb-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Mar-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]Apr-18
[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]May-18
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 1
[/TD]
[TD="class: xl73, bgcolor: transparent"]Fri 01-12-18
[/TD]
[TD="class: xl74, bgcolor: transparent"]Thu 08-03-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]98
[/TD]
[TD="class: xl75, bgcolor: transparent"]31
[/TD]
[TD="class: xl75, bgcolor: transparent"]31
[/TD]
[TD="class: xl75, bgcolor: transparent"]28
[/TD]
[TD="class: xl75, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 2
[/TD]
[TD="class: xl73, bgcolor: transparent"]Fri 09-03-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Sat 17-03-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]9
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]9
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 3
[/TD]
[TD="class: xl73, bgcolor: transparent"]Sun 18-03-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Thu 27-03-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]10
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]10
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 4
[/TD]
[TD="class: xl73, bgcolor: transparent"]Wed 28-03-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Tue 29-05-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]63
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, bgcolor: transparent"]30
[/TD]
[TD="class: xl75, bgcolor: transparent"]29
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Job 5
[/TD]
[TD="class: xl73, bgcolor: transparent"]Wed 30-05-18
[/TD]
[TD="class: xl73, bgcolor: transparent"]Mon 23-07-18
[/TD]
[TD="class: xl75, bgcolor: transparent"]55
[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]2
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks,