With no particular rationale, I have decided to present any computing "things" I know that I feel are worthy of disseminating.
My desktop is fairly old and it runs Office 2003.
More recent versions of Excel than 2003 have an inbuilt function
that returns the ISO Week Number of a date. That week number increments
on Mondays and is week 1 on the week containing the first Thursday (the
majority of the days in week 1 are in the Thursday year).
=ISOWEEKNUM(date)
Excel 2003 provides a week number function (again it can be set to
increment on Mondays), where Jan 1 is always week 1 and weeks may not
all have 7 days.
=WEEKNUM(date,2)
The following code resides entirely in one cell, so certain
functions get called multiple times. This could be avoided by using
multiple cells for the function, but I wanted to use it weekly in a
calendar sheet, so one cell was all I would allot.
The word "date" (in lower case) should be replaced by the relevant cell
number.
If you want a 2-digit (leading zero) display on a numeric cell you can Format Custom as "00".
As a numeric value (use if you want to autofilter on a range of values)(aligned with text version 2 lines below):
=WEEKNUM(date-WEEKDAY(date,3)+3,2)-IF(WEEKDAY(DATE(YEAR(date-WEEKDAY(date,3)+3),1,1),3)>3,1,0)
As text with a leading zero when less than 10:
=RIGHT(100+WEEKNUM(date-WEEKDAY(date,3)+3,2)-IF(WEEKDAY(DATE(YEAR(date-WEEKDAY(date,3)+3),1,1),3)>3,1,0),2)
Returns the date (eg "2025-01-07 Tue" given a year "2025"): Assume year, eg 2025, in cell B2.
=DATE(B2,1,8)-WEEKDAY(DATE(B2,1,5))
My custom cell date format is "yyyy-mm-dd ddd".
If you want another day of the week, alter the last "5" to a value in the range 1..7.
Excel 2003 only has the function RANK. That gives the ranking of a value in a list of N items from 1..N, but if there are several copies of one value all are given the value of the first occurence.
There are two modes, ascending and descending, controlled by the
3rd parameter.
The function is:
=RANK(value,array,mode)
. For example given the (sorted) array "1,2,2,3" (They do NOT need to be sorted.)
RANK(value,array_ref,0) returns array equivalent "4,2,2,1", whilst
RANK(value,array_ref,1) returns array equivalent "1,2,2,4". (Mode 0 has biggest value = 1, Mode 1 has smallest value = 1.
You can create the RANK.AVG function using =(array_size)+RANK(value,array,0)-RANK(value,array,1) and =(array_size)+RANK(value,array,1)-RANK(value,array,0) .
Home >
Steve >
Computing
Last updated 2025-04-21
This page is part of http://www.stocton.org/
Email: webmaster@stocton.org