Array Formula to Generate Bimonthly Dates in Google Sheets

Do you want to generate bimonthly sequential dates in Google Sheets? Look no further! In this article, we’ll explore a simple and efficient way to achieve this using Google Sheets’ built-in functions. Whether you need the dates in ascending or descending order, we’ve got you covered.

How to Get Bimonthly Dates in Ascending Order in Google Sheets

Bimonthly Dates in a Column (Asc)

To generate bimonthly sequential dates in a column, follow these steps:

1. Enter the following array formula in any blank column (e.g., cell C2):

   =filter(sequence(365,1,date(2020,1,1),1),(day(sequence(365,1,date(2020,1,1),1))=16)+(day(sequence(365,1,date(2020,1,1),1))=1))

2. The formula will generate a list of date values. Select the date values (column C) and format them to dates from the format menu (Format > Number > Date).

That’s it! The above array formula will populate the bimonthly sequential dates in Google Sheets from 01-Jan-2020 to 16-Dec-2020.

Bimonthly Dates in a Row (Asc)

If you want to create a Gantt chart template or need to generate bimonthly dates in a row, follow these steps:

1. Use TRANSPOSE with the above formula to get the output in a row:

   =transpose(filter(sequence(365,1,date(2020,1,1),1),(day(sequence(365,1,date(2020,1,1),1))=16)+(day(sequence(365,1,date(2020,1,1),1))=1)))

You can also modify the formula to avoid using TRANSPOSE. Simply replace the values in the ‘rows’ and ‘columns’ arguments of the SEQUENCE function:

=filter(sequence(1,365,date(2020,1,1),1),(day(sequence(1,365,date(2020,1,1),1))=16)+(day(sequence(1,365,date(2020,1,1),1))=1))

This modified formula will generate bimonthly sequential dates in a row.

How to Modify the Formula?

Let’s explore how to modify the formula to populate different bimonthly dates:

1. Different start and end month: To get sequential bimonthly dates from 01-Jun-2020, change date(2020,1,1) in the formula to date(2020,6,1). The formula will return the dates from 01-Jun-2020 to 16-May-2021.

2. Different dates: To get different bimonthly dates, replace 1 and 16 in the formula with your desired dates. For example, replace them with 7 and 23 to generate dates like 07-Jan-2020, 23-Jan-2020, 07-Feb-2020, and so on.

Refer to the image below for a visual explanation:

How to Get Bimonthly Dates in Descending Order in Google Sheets

If you want the dates in descending order (e.g., from December 2020 to January 2020), follow these steps:

1. Use the same formula mentioned earlier and wrap it with SORT:

   =sort(filter(sequence(365,1,date(2020,1,1),1),(day(sequence(365,1,date(2020,1,1),1))=16)+(day(sequence(365,1,date(2020,1,1),1))=1)),1,0)

In a column, the formula would look like this:

=sort(filter(sequence(365,1,date(2020,1,1),1),(day(sequence(365,1,date(2020,1,1),1))=16)+(day(sequence(365,1,date(2020,1,1),1))=1)),1,0)

In a row, we need to use TRANSPOSE as SORT won’t work:

=transpose(sort(transpose(filter(sequence(1,365,date(2020,1,1),1),(day(sequence(1,365,date(2020,1,1),1))=16)+(day(sequence(1,365,date(2020,1,1),1))=1))),1,0))

Formula Logic

To generate bimonthly sequential dates in Google Sheets, I’ve used a simple and logical approach. Here’s how it works:

The SEQUENCE function generates sequential dates, while the FILTER function filters the dates to include only the desired ones (in this case, the 1st and 16th of each month).

That’s all you need to know to generate bimonthly dates in Google Sheets. Feel free to explore the additional resources below for more useful formulas and tips.

Additional Resources

• How to Populate Sequential Dates Excluding Weekends in Google Sheets
• Reset Week Number in Google Sheets Using Array or Non-Array Formulas
• Calendar Week Formula in Google Sheets to Combine Week Start and End Dates
• Weekday Name to Weekday Number in Google Sheets
• How to Find Week Start Date and End Date in Google Sheets with Formula
• How to Find Current Month’s Week Number In Google Sheets
• Populate a Full Month’s Dates Based on a Drop-down in Google Sheets
• How to Auto Populate Dates Between Two Given Dates in Google Sheets
• Find the Past or Future Closest Date to Today in Google Sheets
• Get a Dynamic Date that Advances/Resets in Google Sheets
• How to Convert Date to Month and Year in Google Sheets

Now you have the power to generate bimonthly dates effortlessly in Google Sheets. Happy sheeting!