Date to Period, Week and Day

[shadbolt]

Hello

My company work in 4 week periods from the start of the year.

Cell a1 is todays date

I would like cell b1 to show what period we are in. i.e week 1-4 is period 1, 5-8 is period 2
I would like c1 to show what week of the period we are in i.e 123 or 4
I would like d1 to show what day of the period we are in i.e 123456 or 7

and finally how many working days of the period are left. Small issue is we don't work Sundays so this number will be a max of 24.

Thanks in advance

 

[JLGWhiz]

=INT(WEEKNUM(TODAY())/4)

Or

=ROUNDUP(WEEKNUM(TODAY())/4,0)

the RoundUp function is probably the better of the two. It would consider the one or two week remainder after dividing by 4.

 

[Dave Patton]

Check the suggestion with your data.
Check Excel's help for information on the functions.

T202009b.xlsm
	A	B	C
1	Thu 17-Sep-20
2	10	Period #
3	38	Week #
4	Sun 6-Sep-20	Last day previous period
5	2	Week in period
6	10	Workday in period
7
3a

Cell Formulas
Range	Formula
A2	A2	=ROUNDUP(A3/4,0)
A3	A3	=WEEKNUM($A$1,21)
A4	A4	=WORKDAY.INTL(DATE(2019,12,30),(A2-1)*4,"1111110")
A5	A5	=INT((A1-A4)/4)
A6	A6	=NETWORKDAYS.INTL(A4,A1,11)