How do I get ca calculation to return a 2 digit year?
As they are two digit years if you use Villeroy’s method then I suggest a format code of 00 so you have 01 instead of 1.
The simple way is to just format the cell containing the date as YY, then the underlying date is unchanged, just not all visible. That might not be what you need however.
t90328.ods (30.6 KB)
Formatting vs. value:
A: random dates from 5 decades
B: Plain unformatted year number (1 or 2 digits)
C: Same value as the date in A formatted as 2-digit year “YY”
D: Same value as the number in B formatted with number format code “00”
E: A text value representing the 2-digit year as calculated in B. A 2-digit text is not a number. The sum of this column is 0.
F: The actual, unformatted numeric value of A. All spreadsheet dates are sequential day numbers formatted as dates. The value in 41167 in F1 is equal to the formatted 2012-09-05 in A1 because that date was the 41167th day since day zero. Day zero is 1899-12-30.
Columns G through J do the same operations based on the values in F demonstrating that the formatting has no influence on the calculation result. The year number of 2012-09-05 is the same as the year number of 41167 or $41,167.00 or any other formatted number with the same value.
Thank you, You saved me hours of frustrating attempts to solve this!