Text

Pad Week & Fiscal Period Numbers with Leading Zeros ("2026-W04")

Build fixed-width week and fiscal-period codes with RIGHT, validate integers before padding, and distinguish ISO week-year labels from fiscal periods.

Pad a validated number, not an arbitrary string

For labels with a four-digit year and a two-digit period, ascending text order matches ascending year/period order. Unpadded text such as W10 and W2 does not have that property. Actual charts and PivotTables may use source order or a configured sort instead of text sorting.

B2 must contain the ISO week-numbering year, which can differ from the calendar year near New Year. B3 is the week number. This example formats supplied year/week values; it does not derive them from a date.

An ISO week begins on Monday and belongs to the year containing its Thursday. A year has week 53 when January 1 is Thursday, or when a leap year starts on Wednesday. The guard below checks that rule as well as integer bounds. The supported year range for this example is 1901–9998.

Fiscal periods use separate labels with P, not W. Set B4 to the period and B5 to the configured count (for example 12 or 13). The formula validates numbering but does not determine fiscal start dates or map calendar dates to fiscal periods.

Validated ISO week label in D2

RIGHT is applied only after numeric and integer validation, preventing a value such as 104 from silently becoming 04.

=IF(COUNT(B2:B3)<>2,"Enter numeric year and week",IF(OR(B2<1901,B2>9998,B2<>INT(B2),B3<1,B3>53,B3<>INT(B3)),"Invalid year or week",IF(AND(B3=53,NOT(OR(WEEKDAY(DATE(B2,1,1),2)=4,AND(WEEKDAY(DATE(B2,1,1),2)=3,DAY(DATE(B2,3,0))=29)))),"Year has only 52 ISO weeks",B2&"-W"&RIGHT("00"&B3,2))))

Fiscal period label in D3

B4=4 and B5=13 produce 2026-P04. A 13-period fiscal calendar is distinct from ISO weeks.

=IF(COUNT(B2,B4,B5)<>3,"Enter numeric year, period and limit",IF(OR(B2<1901,B2>9998,B2<>INT(B2),B4<1,B4<>INT(B4),B5<1,B5>99,B5<>INT(B5),B4>B5),"Invalid fiscal period",B2&"-P"&RIGHT("00"&B4,2)))

Compare fixed-width labels

These explicit strings demonstrate text comparison; they do not test chart or PivotTable sorting.

="2026-W04"<"2026-W12"

Try the example

Use the original values or change them to test your understanding.