Target dates to submit reports

[Salamullah]

Target dates excluding holidays (Fri & Sat) of each month to put through formula in plan sheet to get reports from different people, please help me to make a formula in each month based on work days (WKD)

Report WKD Jan Feb Mar ... ....
A 2 2/1/19 4/2/19 4/3/19 ... ....
B 4 6/1/19 6/2/19 6/3/19 ... ....
C 9 13/1/19 13/2/19 13/3/19 ... ....

I have downloaded Calendar Template from excel and wanted to apply formula from it.

 

[sergioMabres]

Hi Salamullah
If first due date is in A2 use the formula en B2 for next month due date

[COLOR=#444444][FONT=Calibri]=IF(WEEKDAY(EDATE(A2,1))=7,EDATE(A2,1)+1,IF(WEEKDAY(EDATE(A2,1))=6,EDATE(A2,1)+2,EDATE(A2,1)))[/FONT][/COLOR]

Cheers
Sergio

 

[Salamullah]

Hi Salamullah
If first due date is in A2 use the formula en B2 for next month due date

[COLOR=#444444][FONT=Calibri]=IF(WEEKDAY(EDATE(A2,1))=7,EDATE(A2,1)+1,IF(WEEKDAY(EDATE(A2,1))=6,EDATE(A2,1)+2,EDATE(A2,1)))[/FONT][/COLOR]

Cheers
Sergio

Hi Sergio,

Many thanks

I am a little bad student of excel so unable to understand correctly !

while applying your given formula in B2 based on A2 which is work day 2 the result is coming 2nd Feb but it should come 4th Feb because 1st is Friday and 2nd is Sat (holidays), 2nd work day is Monday which is 4th of Feb !
I have formatted cell B2 with date
Please see also when I copied the formula in C2 for March its not giving 4th Mar.

 

[sergioMabres]

Hi Salamullah,
The problem is WEEKDAY that works different in every language
So I used the standard that is:
WEEKDAY accepts a date and returns a number between 1-7 representing the day of week for that date. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, as shown in the table below:
Result Meaning
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

The formula uses WEEKDAY(EDATE(A2,1))=7 for Saturday and WEEKDAY(EDATE(A2,1))=6 to avoid Fridays and then add 1 and 2 to go to a Sunday, but now I realize that you want a Monday

First please check in you machine what number you get for a Friday with the formula in B3 =WEEKDAY(A3) and in A3 write a date that is Friday, then tell me.

Next as you want to to lay in Monday not a Sunday the formula should be:

[COLOR=#444444][FONT=Calibri]=IF(WEEKDAY(EDATE(A2,1))=7,EDATE(A2,1)+2,IF(WEEKDAY(EDATE(A2,1))=6,EDATE(A2,1)+3,EDATE(A2,1)))[/FONT][/COLOR]

Cheers
Sergio

 

[Salamullah]

Hi Sergio,

Number is 6 for Friday

 

[sergioMabres]

Good 6 for Friday is good, use the corrected formula in B2 and when A2 has the January due date

[COLOR=#444444][FONT=Calibri]=IF(WEEKDAY(EDATE(A2,1))=7,EDATE(A2,1)+2,IF(WEEKDAY(EDATE(A2,1))=6,EDATE(A2,1)+3,EDATE(A2,1)))[/FONT][/COLOR]

Then copy right and down
If it does not work, please tell me the date you get so I can correct what is wrong
Cheers
Sergio

 

[Salamullah]

Hi Sergio

The result coming is 2n Feb which is Sat in calendar

I put target work day number 2 in A2 and applied formula in B2 whereas actual date should be Monday 4th Feb similarly if I put 6 in A3 the formula is giving 6th Feb instead of 10th Feb.

 

[sergioMabres]

That is very strange see my test file at https://1drv.ms/x/s!AovCE1fDrrdSmFJcN0k8fUc3G5UE
Works fine, keep in mind that english setings shows dates as m/d/yyyy
Jan Feb Mar Apr May
1/2/2019 2/4/2019 3/4/2019 4/4/2019 5/6/2019
1/6/2019 2/6/2019 3/6/2019 4/8/2019 5/8/2019
Cheers

 

[Salamullah]

Hi Sergio,

Its working many thanks, I think I have not explained correctly,
the formula you gave is dependent on previous month, where as I have fixed number in each line in column A and formula required on that number for all months

like if number is 8 the date in Apr is 10 & 12 in May.

 

[sergioMabres]

I see, please look now if that is what you need, look in https://1drv.ms/x/s!AovCE1fDrrdSmFJcN0k8fUc3G5UE
Cheers
Sergio