Intersecting dates for 14/14 schedule

[ajverdin]

I am a boat captain. I wish to create a worksheet that will calculate the number of daysI have worked based upon a give start date and a given end date. Butwait, it’s not that simple to just subtract the start date form the enddate. I have two columns. One column contains the start date, theother contains the end date. The first and last day counted must equal .5of a day, all other dates counted are represented as a 1. I am paid for the days that fall between the1^st thru 15^th and the 16^th thru the end of themonth. <o></o>
Days which fall in-between the 1^st & 15^th<o></o>
1/1/2017-1/8/2017 would equal to 7 days, the first &last day equals .5 of a day. <o></o>
1/5/2017-1/12/2017 also equals 7 days<o></o>
1/1/2017-1/15/2017 equals 14 days<o></o>
<o> </o>
1/10/2017-1/24/2017 this situation equals 5.5 days forthe 1^st thru the 15^th pay period & 8.5 days for thesecond column.<o></o>
<o> </o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 125, bgcolor: transparent"] Start date<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] End Date<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 1^st thru 15^th<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 16^th thru EOM<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] Total <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 125, bgcolor: transparent"] 1/4/2017<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 1/18/2017<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 11.5 days<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 2.5 days<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 14 days<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 125, bgcolor: transparent"] 2/22/2017<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 3/8/2017<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 6.5 days<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 7.5 days<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 14 days<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 125, bgcolor: transparent"] 3/22/217<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 4/5/2017<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 9.5 days<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 4.5<o></o>
[/TD]
[TD="width: 125, bgcolor: transparent"] 14 days<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 125, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 125, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 125, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 125, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 125, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o> </o>
To better explain my situation, I work a 14/14 schedule (14days at work, 14 days home). Keep inmind, the name 14/14 is slightly misleading because I am actually working for15 consecutive days. <o></o>
My two formulas that I have come up with but they do notfunction correctly for all scenarios<o></o>
<o> </o>
=IF(AND(DAY(G8)=1,DAY(H8)=15),H8-G8,IF(MONTH(H7)=MONTH(G8),EOMONTH(DAY(G8),0)-DAY(G8)+1-0.5,IF(DAY(G8)>DAY(15),DAY(G8)-DAY(16)+0.5,DAY(15)-DAY(G8)+0.5)))<o></o>
=IF(AND(DAY(StartDate2)=1,DAY(EndDate2)=15),14,IF(AND(MONTH(EndDate2)=MONTH(StartDate2),DAY(StartDate2)>=16),EndDate2-StartDate2+290,IF(MONTH(StartDate2)<MONTH(EndDate2),EOMONTH(DAY(StartDate2),0)-DAY(StartDate2)+0.5,DAY(16)-DAY(StartDate2)-0.5)))<o></o>

 

[mole999]

to accomodate .5 on each end day, could you not take the start date from the end date - 1 or is that too simple

 

[ajverdin]

I made a mistake in the first post. The formula I am having trouble with is as follows.
=IF(AND(DAY(G8)=1,DAY(H8)=15),14,IF(AND(MONTH(H8)=MONTH(G8),DAY(G8)>=16),H8-G8,IF(MONTH(G8)<MONTH(H8),EOMONTH(DAY(G8),0)-DAY(G8)+0.5,IF(AND(DAY(H8)<15,MONTH(H8)=MONTH(G8)),H8-G8+0.5,DAY(16)-DAY(G8)-0.5))))

 

[ajverdin]

That would work in practice, but there are occasions when subtracting a whole number for the range will impact the first column. The above suggestion would not represent the actual number of days to be paid within the given ranger of the 1 thru the 15.