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
Bookmarks