Auto populate date
Is there a way to auto-populate a date within a column, specifically the second Tuesday of every month?
Best Answer
-
There is no way to get it to auto-populate without a helper column containing dates. If you use two helper columns, we can get it put together with relatively little work.
First would be a text/number column (called "Number" in this example). You can enter 0 in the top row and 1 in the second row then highlight both cells, grab the bottom right corner, and dragfill as far down as you want to go (within sheet limits of course).
You will need to pick a starting year and month (indicated in bold below), but then you can use this formula in a date type column (called "Date" in this example) to populate the first of every month increasing in relation to the Number column.
=IFERROR(DATE(start_date_year + ROUNDDOWN((start_date_month + Number@row) / 12, 0) + IF(IF(MOD(start_date_month + Number@row, 12) = 0, 12, MOD(start_date_month + Number@row, 12)) = 12, -1) - IF(AND(ABS(Number@row) - start_date_month <> 12, Number@row < 0, ABS(Number@row) > start_date_month), 1, 0), IF(MOD(start_date_month + Number@row, 12) = 0, 12, MOD(start_date_month + Number@row, 12)), 1), DATE(IF(start_date_month - ABS(Number@row) < 1, start_date_year - 1, start_date_year), IF(start_date_month - ABS(Number@row) < 1, start_date_month + (12 - ABS(Number@row)), start_date_month - ABS(Number@row)), 1))
Then in the next date type column you would use this to output the second Tuesday of the month:
=DATE(YEAR(Date@row), MONTH(Date@row), 1) + (IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) <= 3, 3, 3 + 7) - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1))) + 7
EDIT TO UPDATE: Both of the formula above can be applied as column formulas
Answers
-
Can you explain this better?
-
There is no way to get it to auto-populate without a helper column containing dates. If you use two helper columns, we can get it put together with relatively little work.
First would be a text/number column (called "Number" in this example). You can enter 0 in the top row and 1 in the second row then highlight both cells, grab the bottom right corner, and dragfill as far down as you want to go (within sheet limits of course).
You will need to pick a starting year and month (indicated in bold below), but then you can use this formula in a date type column (called "Date" in this example) to populate the first of every month increasing in relation to the Number column.
=IFERROR(DATE(start_date_year + ROUNDDOWN((start_date_month + Number@row) / 12, 0) + IF(IF(MOD(start_date_month + Number@row, 12) = 0, 12, MOD(start_date_month + Number@row, 12)) = 12, -1) - IF(AND(ABS(Number@row) - start_date_month <> 12, Number@row < 0, ABS(Number@row) > start_date_month), 1, 0), IF(MOD(start_date_month + Number@row, 12) = 0, 12, MOD(start_date_month + Number@row, 12)), 1), DATE(IF(start_date_month - ABS(Number@row) < 1, start_date_year - 1, start_date_year), IF(start_date_month - ABS(Number@row) < 1, start_date_month + (12 - ABS(Number@row)), start_date_month - ABS(Number@row)), 1))
Then in the next date type column you would use this to output the second Tuesday of the month:
=DATE(YEAR(Date@row), MONTH(Date@row), 1) + (IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) <= 3, 3, 3 + 7) - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1))) + 7
EDIT TO UPDATE: Both of the formula above can be applied as column formulas
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 505 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!