Excel Formula for dates

[hrayani]

Hello Friends,

Kindly have a look at the below and provide a solution

Weekly Calendar NEW.xlsm
	B	C	D	E	F	G	H	I	J	K
2
3		WEEKLY TARGETS			YEAR	2017	WEEK #	1
4
5		CATEGORY	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
6
7		Alpha
8		Bravo
9		Charlie
10
11		Dates required in Range D6:J6 with the following criteria
12		1) If first day of the month starts on Friday then Monday should show 4th day and should be treated as week # 1
13		2) If first day of the month starts before Friday then that should be treated as week # 1
14		3) If user changes year or week # then dates should change considering point number 1 and 2
15
16		Example
17		Year enterd is 2017 > 1st day of this year is Sunday
18		Required Answer if week # 1 is entered
19		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
20		02 - Jan - 2017	03 - Jan - 2017	04 - Jan - 2017	05 - Jan - 2017	06 - Jan - 2017	07 - Jan - 2017	08 - Jan - 2017
21		Required Answer if week # 3 is entered
22		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
23		16 - Jan - 2017	17 - Jan - 2017	18 - Jan - 2017	19 - Jan - 2017	20 - Jan - 2017	21 - Jan - 2017	22 - Jan - 2017
Weekly Schedule (4)

Regards,

Humayun

 

[AhoyNC]

See this link for formula:

Excel WEEKNUM function – convert week number to date and vice versa

Formula examples demonstrate how you can use the WEEKNUM function to calculate week numbers in Excel - convert week number to date, get week number from date, sum values based on the week number and more.

www.ablebits.com

Book1
	A	B	C	D	E	F	G	H	I
1
2		WEEKLY TARGETS			YEAR	2017	WEEK #	1
3
4		CATEGORY	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
5			1/2/2017	1/3/2017	1/4/2017	1/5/2017	1/6/2017	1/7/2017	1/8/2017
Sheet2

Cell Formulas
Range	Formula
C5	C5	=DATE($F$2,1,-2)-WEEKDAY(DATE($F$2,1,3))+$H$2*7
D5:I5	D5	=C5+1

 

[hrayani]

Hello AhoyNC,

I was scratching my head over this for the last 2 days ... and you did it in minutes

Thanks a lot

Regards,

Humayun

 

[AhoyNC]

You're welcome. Glad that worked for you. Thanks for the feedback.

 

[hrayani]

Hello Friends,

Now I will be making a monthly calendar.. I need help with it.

Weekly Calendar Final.xlsx
	B	C	D	E	F	G	H	I
2	MONTHLY TARGETS			Month	1	YEAR	2017
3
4	WEEK	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
5
6
7		1	2	3	4	5	6	7
8
9		8	9	10	11	12	13	14
10
11		15	16	17	18	19	20	21
12
13		22	23	24	25	26	27	28
14
15		29	30	31	1	2	3	4
16
17
18	Dates required in range C5:I5
19	In said case Sunday should show 1st Jan 2017 and Saturday should show 31st Dec 2016
20
21	Week # required in green cells
Monthly (2)

Cell Formulas
Range	Formula
C7,C15,C13,C11,C9	C7	=I5+1
D15:I15,D13:I13,D11:I11,D9:I9,D7:I7	D7	=C7+1

Regards,

Humayun

 

[hrayani]

Hello Friends,

I have figured out the date issue by watching a video on youtube link excel dynamic calendar - Google Search

Now the week part remain pending. I want the week # in range B5:B10

If first day of the year starts on or before Thursday then it should be considered as week # 1 otherwise it should be considered as week 52 or 53 of last year
In below case the second row should show week # 1 and first row should show last week of last year

Weekly Calendar Final.xlsx
	B	C	D	E	F	G	H	I
2			Month	January		YEAR	2021	1-Jan-21
3	January 2021
4	WEEK	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5		27	28	29	30	31	1	2
6		3	4	5	6	7	8	9
7		10	11	12	13	14	15	16
8		17	18	19	20	21	22	23
9		24	25	26	27	28	29	30
10		31	1	2	3	4	5	6
Monthly

Cell Formulas
Range	Formula
I2	I2	=DATE(H2,VLOOKUP(E2,J4:K10,2,0),1)
B3	B3	=E2&" "&H2
C5	C5	=IFERROR(I2-(WEEKDAY(I2)-1),"")
D5:I5	D5	=IF(C5<>"",C5+1,$I4+1)
C6:C10	C6	=I5+1
D6:I10	D6	=C6+1

Any help would be appreciated.

Regards,

Humayun