0
I am trying to do a function that identifies which week of the month belongs to which day and, if the first day is at the end of a week, that it is counted as the day of the previous week, that is, the last of the previous month. And if the last days are at the beginning of a week, they enter the week of the following month. That way, there wouldn’t be weeks with less than seven days. I managed to do something using the formulas SE, E, OU and DIA.DA.SEMANA. However, I cannot make that, by grouping day 1 as the day of the fifth week of the previous month, week 2 becomes week 1, week 3 as 2 and so on. Below is the formula I created:
=SE(OU(E(OU(DIA.DA.SEMANA(A2)=5;DIA.DA.SEMANA(A2)=6;DIA.DA.SEMANA(A2)=7);MÊS(A2-1)<>MÊS(A2));E(OU(DIA.DA.SEMANA(A2)=6;DIA.DA.SEMANA(A2)=7);MÊS(A2-2)<>MÊS(A2)));5;SE(OU(E(DIA.DA.SEMANA(A2)<4;MÊS(A2+1)<>MÊS(A2));E(DIA.DA.SEMANA(A2)<3;MÊS(A2+3)<>MÊS(A2)));1;NÚMSEMANA(A2)-NÚMSEMANA(DATA(ANO(A2);MÊS(A2);1))+1))
If you have any solution (by VBA or even by excel formulas) it would help a lot!
Working with weekdays is very complicated, especially at the turn of the year... For this there is the ISO 8601-1:2019, I suggest using this model. If you want to see how Ron de Bruin does in VBA see this link. From Excel 2013 there is the function ISO
– danieltakeshi
I understand. Thank you so much for your help!
– Felipe Jaworoski