There have been posts about adding days and months but how do I add a whole year when listing dates in a column ? Trying to show the days of week for a number of birthdays going forward and backward.
Just adding 12 months doesn't get a accurate date and nor does adding 365 days (because of leap years) !
Help !
Mac Pro, macOS High Sierra (10.13.6), Showing my age when I admit my first Mac was a SE...
Hi SRM,
Enter dates in Column A.
Extract the Year, Month, Day in Columns B, C, D.
Formula in B2 =YEAR(A2)
Formula in C2 =MONTH(A2)
Formula in D2 =DAY(A2)
Use the DATE function in Column E with this syntax DATE(year, month, day) and adding 1 to the year
Formula in E2 =DATE(B2+1,C2,D2)
The year 2000 was a Leap Year according to the Mac Calendar App 😎.
You can hide the intermediate Columns B, C, D.
Regards,
Ian.
Hi SRM,
Enter dates in Column A.
Extract the Year, Month, Day in Columns B, C, D.
Formula in B2 =YEAR(A2)
Formula in C2 =MONTH(A2)
Formula in D2 =DAY(A2)
Use the DATE function in Column E with this syntax DATE(year, month, day) and adding 1 to the year
Formula in E2 =DATE(B2+1,C2,D2)
The year 2000 was a Leap Year according to the Mac Calendar App 😎.
You can hide the intermediate Columns B, C, D.
Regards,
Ian.
Hi Still'
Adding 12 months does get an accurate date if you do it using the EDATE function.
The formula shown below the table is in cell B2.
Cell C2 contains the same formula, with 24 in place of 12.
Cell D2 contains the same formula with 36 in place of 12.
All three formulas are filled down to the end of their respective columns.
Note that the formulas will return an error message (or a strange result) if the entry in that row of column A is not a date or there is no entry in that row of column A.
All cells are formatted the show only the Date part of the Date and Time value in the cell, and to include the short form or the weekday name for that date.
The revised table shown below includes a test of Feb 29 in row 11. Note that the year in A11 is 2016, the most recent year in which there was a February 29.
I added a fifth column to include the next leap year after 2016, and made the two changes shown in that column, and described below.
In cells E1, I entered the number 4 in place of the text string '4 years'. This made possible the change in the formula placed in cell E2, and shown below the table.
Instead of writing the number of months into the formula, this formula calculates the number from 12 and the number of years (in E1). With the labels in row 1 replaced with numbers 1, 2, 3… this formula can be entered in B2, then filled down and right to all cells below and to the right of it, where it will automatically adjust to fit the new locations.
Regards,
Barry
Adding a whole year
