M
mlv
Hi
One of the fields in an Excel timesheet I use calculates the current week
number.
The formula is :
=ROUNDUP(((Q9-DATE(YEAR(Q9),1,0))/7),0)
Cell Q9 (referenced by the week number calculation) contains the following
formula that calculates Friday's date for the current week :
=IF((TODAY()-36651)/7-INT((TODAY()-36651)/7)>0,36651+7*INT((TODAY()-36651)/7)+7,TODAY())
The function works OK, except I usually have to tweak the week calculation
formula annually, depending on when the new year starts. Generally changing
ROUNDUP to ROUNDDOWN, or vice versa is all that is needed.
At the moment, this calculation seems to increment to the next week number
on the Sunday of each week, when it in fact should not increment until the
Monday
Is there a more elegant way of accurately calculating the week number
without the need for annual tweaking?
Thanks
One of the fields in an Excel timesheet I use calculates the current week
number.
The formula is :
=ROUNDUP(((Q9-DATE(YEAR(Q9),1,0))/7),0)
Cell Q9 (referenced by the week number calculation) contains the following
formula that calculates Friday's date for the current week :
=IF((TODAY()-36651)/7-INT((TODAY()-36651)/7)>0,36651+7*INT((TODAY()-36651)/7)+7,TODAY())
The function works OK, except I usually have to tweak the week calculation
formula annually, depending on when the new year starts. Generally changing
ROUNDUP to ROUNDDOWN, or vice versa is all that is needed.
At the moment, this calculation seems to increment to the next week number
on the Sunday of each week, when it in fact should not increment until the
Monday
Is there a more elegant way of accurately calculating the week number
without the need for annual tweaking?
Thanks