# split date range into date ranges of corresponding months

1. ## split date range into date ranges of corresponding months

Hy,

I need FROM 4 parts of vacation - CAN BE LESS (8 columns) in a sheet
to extract date ranges for each month and match it with a name
ex: vacation
20.10.2017 - 12.11.2017 needs to be splitted (or not if it\s in only one month) into 20-31.10 and 01-12.11

I have calculated vacation for people based on entered number of vacation days and starting date

=IFERROR(WORKDAY(WORKDAY(EDATE(\$I4;0);-1);\$H4+IF(AND(IF(AND(WEEKDAY(\$D4)<>1;WEEKDAY(\$D4)<>7);\$D4;0)>=\$I4;IF(AND(WEEKDAY(\$D4)<>1;WEEKDAY(\$D4)<>7);\$D4;0)<=\$J4);1;0)+IF(AND(IF(AND(WEEKDAY(DATE(YEAR(\$D4)+1;MONTH(\$D4);DAY(\$D4)))<>1;WEEKDAY(DATE(YEAR(\$D4)+1;MONTH(\$D4);DAY(\$D4)))<>7);(DATE(YEAR(\$D4)+1;MONTH(\$D4);DAY(\$D4)));0)>=\$I4;IF(AND(WEEKDAY(DATE(YEAR(\$D4)+1;MONTH(\$D4);DAY(\$D4)))<>1;WEEKDAY(DATE(YEAR(\$D4)+1;MONTH(\$D4);DAY(\$D4)))<>7);(DATE(YEAR(\$D4)+1;MONTH(\$D4);DAY(\$D4)));0)<=\$J4);1;0);PRAZNICI!\$A\$2:\$A\$20);"-")

anyone can use whole vacation in four parts (or less), so i have number of vacation days per part, beggining and end of vacation.

I'm calculating count of people on a vacation like so

=SUMPRODUCT(--(SVI!\$I\$4:\$I\$295<=A\$2);--(SVI!\$K\$4:\$K\$295>=A\$2))+SUMPRODUCT(--(SVI!\$N\$4:\$N\$295<=A\$2);--(SVI!\$P\$4:\$P\$295>=A\$2))+SUMPRODUCT(--(SVI!\$S\$4:\$S\$295<=A\$2);--(SVI!\$U\$4:\$U\$295>=A\$2))+SUMPRODUCT(--(SVI!\$X\$4:\$X\$295<=A\$2);--(SVI!\$Z\$4:\$Z\$295>=A\$2))

i have a pattern for print and i need to split vacation by months (from first of the month or to last of a month) and match it with names of people

i was trying somenthing like this (in example file):
temporarily
=A1
=IF(MONTH(\$B1)=MONTH(\$A1);B1;EOMONTH(\$A1;0))
=IF(MONTH(\$B1)=MONTH(\$A1);"";EOMONTH(\$A1;0)+1)
=IF(MONTH(\$B1)=MONTH(\$A1);"";\$B1)

and then to match it with a month
=IF(MONTH(D1)=MONTH(\$K\$10);D1;"")
=IF(MONTH(F1)=MONTH(\$M\$10);F1;"")

Does anyone has a better solution?
Thanks  Register To Reply

2. ## Re: split date range into date ranges of corresponding months

The sample file attached only shows data relevant to the last part of your query, so it's impossible to answer. This is probably why you have had no help offered so far.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 