Setting different YEAR in Numbers DATE cell Formatting

Hi petrusse,

Automatic year setting is a convenience feature that includes the year when you enter only the month and day. There is no user setting to specify the year except to enter the year with the month and day.

That said, it is easy, given a date in the current year to calculate the same date in a previous year using the EDATE function.

EDATE(start date,n) returns the date n months after start date.

If n is negative, the date returned is n months before the start date.

Formula shown is entered in A2, and filled down the rest of that column. The IF statement places the text message in its cell if there is no entry made in that row of column B. When an entry is made in column B, EDATE calculates the date 12 months earlier and places the result in the cell containing the formula.

At the end of a session, select the cells containing dates in column A, Copy, then go to th Edit menu and choose Paste Formula Results to replace the formula with the calculated dates.

Column B can be hidden when not in use.

Regards,

Barry

Hi petrusse,

You can use the DATE function.

Formula in C2 =IF(OR(A2="",B2=""),"",DATE(Year::$A$1,A2,B2)) and fill down.

The IF(OR(A2="",B2=""),"" part tests for blank cells in columns A and B and inserts "" (NULL) if either is blank.

Else (they are not both blank), insert the DATE (year, month, day).

Beware of Leap Years with this method!

Regards,

Ian.

"…And when i am finished i can just copy the entire date column and re-past match style and delete column B"

Close, but if you Paste and Match Style, then delete column B, all the entries in Column A will show error triangles.

The Paste command you want is Paste Formula Results, which pastes the results (dates) but not the formula that produced those results. You can set the format on column A before or after doing Copy > Paste Formula Results.

Regards,

Barry