Auto populate date

Alanoy

Is there a way to auto-populate a date within a column, specifically the second Tuesday of every month?

Paul Newcome

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

BullandKhmer

Can you explain this better?

Paul Newcome

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