create specific event at specific time every week

i would like to know how can do the following
i have some even that occur at specific time every week
i have workday for one year
and i want to select specific month which have four week

the even is the following
1- occur only one time / month (in the first working day of the month )
2- occur one time every / week (in the first working day of first week)
3- occur one time every / week ( in the second working day of first week)
4- occur one time every / week ( in the third working day of first week)
5- occur one time every / week ( in the fourth working day of first week)
6- occur one time every /week ( in the five working day of first week )
I need function in cell F that related to data in column E in sheet sample according to working day in refer sheet in column G

I ordered the data by date/event with C1 being the initial event.

in F3

=DATE(C2,MONTH(B2&0),1)+CHOOSE(WEEKDAY(DATE(C2,MONTH(B2&0),1),1),7,6,5,4,3,2,1)

calculates first SUNDAY (as this appears to be your first work day)

in F4

=$F$3+(MATCH(First,Week,0)-1)*7

This calculates the first day of a given week

"Week" is a named range

For 2nd day of week

=$F$3+(MATCH(First,Week,0)-1)*7+1

Dear JohnTopley

i note that when i select August in B2 is started from 7-8 but actually first working day in this month is 1-8
also i this it will be good if it depend upon column G In refer sheet because its contain working days after exclude weekend ( Friday and Saturday) and holidays .
can please check and see what we can do
thanks for your attention and concern
happy day

also i note that something in function in refer sheet in G4 , its exclude some working days
please see my attached photo

if this comment is correct

in September first day is Thursday equal 1 but the equation give me direct 4 Sunday

this photo for correct data for august

it must be like this

There is no consistency in calculating the first working day of the month: for "August" you now say it is the end of July.

What about September where have 1st September then the 4th : what is the output here.

its rules that sample taken every week in the same manner of attached phtoo

1 September will taken to complete for august month
but in September will be start taken from 4

the problem appear in august because it must start from 31 not from 7

hope you understand me now

I understand that generally we start on the first Sunday of the month: it is the anomalies that cause the problem

31 st July for August
4th September is OK as it is the first Sunday.
3rd October is a MONDAY (1st/2nd are holidays) so do we ignore the Monday?
30th October is another potential problem as it the NEAREST Sunday to 1st November.

This where the problem lies: the inconsistencies.

What is your advice to overcome this problem

Originally Posted by JohnTopley

I understand that generally we start on the first Sunday of the month: it is the anomalies that cause the problem

31 st July for August
4th September is OK as it is the first Sunday.
3rd October is a MONDAY (1st/2nd are holidays) so do we ignore the Monday?
30th October is another potential problem as it the NEAREST Sunday to 1st November.

This where the problem lies: the inconsistencies.

See the attched.

I added a column H in "REFER" which has a month nuumber against the first date for a month. The "only" issue is October where there is no initial Sunday.

in F3 of "Sample"

=INDEX(' refer'!G4:G250,MATCH(MONTH($D$2),' refer'!$H$4:$H$250,0))

this finds the starting date for Sunday of week 1.

I'll sign off for today and re-start tomorrow.

Dear Johntoply
i would like to appreciate your effort and your concern .
please kindly see my attached photo because its give another error

hope we can get good solution

The entry in column H should correspond to the month so you have 7 when the month is January so if you have 8 for February then when you select August (the 8th month) you get FEBRUARY. So January should have 1 and February have 2 etc.

8 was placed on 31st July because 31st July was the first Sunday for the AUGUST period:

Using the above method I proposed i.e. identify the FIRST week for a month, then the solution I gave earlier should work.

please i cant understand can explain more or give me example

The example was given in the last file I posted.

You set the first Sunday of each month to the numeric value of that month in column H.

So 3rd July is set to 7, 31st July set to 8, 4th September set to 9 etc.

this formula ..

=INDEX(' refer'!G4:G250,MATCH(MONTH($D$2),' refer'!$H$4:$H$250,0))

matches month in date in D2 to find the start date in column G of "Refer" using the value in column H.

dear johnTopley
please can see attached photo that G4 in refer sheet is started from 2-1-2017 although 1-1-2017 is working day (sunday)

please can check my attached file for number add to column H if it correct and that you mean

this number can be make automatic because every time i must be change it manual
if there is any formula that count first sunday every month and give it number in Column H

See the attached which marks the first Sunday in each month.

in E6

=YEAR(F4) Year we are considering

in F7

=DATE($E$6, ROW(1:1),1)+6-WEEKDAY(DATE($E$6, ROW(1:1), 1),3)

Copy down

This a list of the first Sunday for each month in the chosen year

In H4

=IFERROR(IF(MATCH($G4,$F$7:$F$18,0),MONTH($G4),""),"")

Copy down

This flags the first Sunday of each month with month number (1, 2, 3 etc)

please see my attached photo that give me friday as working day in first day of the year

This is issue with WORKDAY.INTL

Try

in G4

=IF($F$4<$F$5,WORKDAY.INTL(EOMONTH($F$4,-1),1,7,OFFSET($E$4,,MATCH(YEAR($F$4),$E$3:$CP$3,0),30,1)),"")

Give all the time I have spent on this, I think it is not unreasonable that you try and resolve these issues yourself.

Use "Google" to find in these issues are known.

there is some modification if can be do in formula in sheet Sampl
as attached photo

Sorry but there are always going to exceptions like this with no common factor (logic) to determine when it should be changed.

i would like to know can't add some function that give this probalility ( function that if the Sunday not started in first week and coming in second week like in our example coming in 7-1 , in this case look for previous sunday in previous month (31-12-2017)

What happens 2019 when 6th January is first Sunday, or 2020 when 5th January is first Sunday??????

in 2019 it will be 30-12-2018
in 2020 it will be 29-12-2019
this what i mean
it just search for previous sunday (in previous month ) not search for number but search for Sunday and start from this

the Rule is take sample every week stared from Sunday till Thursday

So if the January start date is ANYTHING other than the 1st of January we go back 7 days into December?

This is the FINAL version:

D3 contains the TRUE first Sunday in month.

in F3

=IF(AND(MONTH(D3),DAY(D3)>1),D3-7,D3)

Checks if NOT first of January then got back to previous Sunday.

If you want anything different you should now be able to work out the logic yourself.

please see my photo

and started from 28-1-2018 not 28-12-2017

My mistake .... but you work out why as I copied the cell from F3 to D3: look at the formula in the cell.

not understand what you mean exactly

and why you start from from E4 although data started from E6

HTML Code: 

You haven't worked out how this formula works!!

should be

=INDEX(' refer'!$G$4:$G$250,MATCH(MONTH($D$2),' refer'!$H$4:$H$250,0))

really thank you Dear JohnTopley for all your effort and all attention
i will test the file and if have any think i will send you
really thanksssssssssss