Hy,
I have names in the rows and 4 periods (dates representing max 4 parts of yearly vacation) in columns
need to convert and split that 4 periods (beginning date and ending date * 4) by monthly columns...
A B C D E F G H I
name1 - 03.03.2018 - 20.03.2018 - empty - empty - 20.04.2018 - 05.05.2018 - empty - empty -
name2 - 25.03.2018 - 20.04.2018 - empty - empty - 20.06.2018 - 25.06.2018 - empty - empty -
name3 - 03.03.2018 - 20.03.2018 - empty - empty - 20.04.2018 - 05.05.2018 - empty - empty -
result
... feb mar apr may jun jul ... (month and year)
name1 - 03-20.03 20-30.04 01-05.05
name2 - 25-31.03 01-20.04 20-25.06
name3 - ....
Is there any way to do this?
i tried
=IF(MONTH($B2)=MONTH($I$1);INDEX($B$2:$C$4;MATCH(H2;$A$2:$A$4;0);1);"")
and
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)
then
=IFERROR(IF(MONTH(D1)=MONTH($K$10);D1;IF(MONTH(H1)=MONTH($K$10);H1;""));"")
=IFERROR(IF(MONTH(F1)=MONTH($M$10);F1;IF(MONTH(J1)=MONTH($M$10);J1;""));"")
but I cant put it all togeather
Thanks
Bookmarks