How to use WEEKNUM function with 1st week starting mid-year?

[evanlamarr88]

Hello!

I need a running week number of employment for staff, but often need the 1st week to start mid-year due to new employees. I consequently need to be able to change the date of the 1st week as well, as new employees come on staff.

Is it possible to use the WEEKNUM function to do so, and if so, could anyone help me understand how to modify it so I can start the week number from any date? Example file is attached. For context, all staff members need to spend 10% of their time on a particular project.

 

[Chihiro]

Can't you do something like...
=NETWORKDAYS(TODAY(),DATE(2016,4,25))/5
=12 work weeks

Week# for others are bit off as well.
Week # returned by WEEKNUM(), considers Jan 1, 2016 as Week 1, although it fell on Friday and Week#1 had no work day (i.e. week# really should be 28 for the rest).

I'm bit confused as to how you derived Hrs. worked/year
Since Gromley started on Apr 25, 2016. Remaining workdays without considering Holidays and Time off. He could potentially work 1440 hours in 2016.

I'd recommend building table of Holidays to accurately reflect workdays in a period as well.

 

[Hui]

In 2016 The first week starting on a Sunday started on 3 Jan
The first week in July started on 3 July
That is 182 days later
So to make any date a week starting after 3 July simply =Weeknum(Date-182,1)
eg:
for Today 15 July 2016
=Weeknum(Today()-182,1)
=3

 

[evanlamarr88]

Thanks for your response @Chihiro !

I was able to use =NETWORKDAYS(DATE(2016,4,25),TODAY(),)/5 and it worked perfectly! Thank you! I was unaware of that formula.


Hours worked/year is weekly hours x 52 wks/year. Column G =(Column C-Columns D,E,F) x .1.


However, how will I change the WEEKNUM function for the rest of the staff to be correct? I entered 15 into the WEEKNUM formula base off this website since the week began on a Friday, and it still returned 29.

 

[evanlamarr88]

@Hui ,

Not quite following your logic - likely a problem on my end. However, there were 116 days between 1/1/16 & 4/25/16 including the end date, so I used your formula =WEEKNUM(TODAY()-116,1), and it returned 13, not 12 as it actually is by manually counting.

 

[Chihiro]

If using Excel 2013 or later you can use ISOWEEKNUM function.

=ISOWEEKNUM(DATE(2016, 1, 1))
Will return 53.
=ISOWEEKNUM(Today())
Will return 28.

=WEEKNUM(DATE(2016, 1, 1), 2)
Will return 1.
=WEEKNUM(TODAY(),2)
Will return 29.

 

[evanlamarr88]

thanks again, @Chihiro !

I was able to take your advice and created the sheet attached. I could perhaps create a holiday chart as you mentioned another time, as it sounds beneficial, but I only have so much time to create excel sheets.