How to put dates in order in Excel. Create a list of consecutive dates.

You can quickly create a list of dates in sequence using Fill marker or command fill in.

    Select the cell on the sheet where you want to start the list consecutive dates, and enter the first day or date.

    For example enter Monday or 6/15/08 .

    Follow the steps below to create a list of dates.

Advice: Like any other data, dates can be sorted. By default, dates are sorted in order from earliest to latest.

additional information

You can always ask the Excel Tech Community a question, ask for help from the Answers community, or suggest a new feature or improvement on the Excel User Voice website.

Notes:

  • Disclaimer regarding machine translation... This article was translated using a computer system without human intervention. Microsoft offers these machine translations to help users who don't know of English language, read materials about Microsoft products, services, and technologies. Since the article was translated using machine translation, it may contain lexical, syntax and grammatical errors.

If you are working with time-based data, you may need to insert a number of dates into the table. For example, in a sales tracking app, you can enter a series of dates for a quarter, making sure of course to skip dates that were over the weekend.

Using autocomplete

Most effective method enter multiple dates, which does not require any formulas - using the auto-fill feature. Enter the first date, and then drag the cell fill handle while pressing the right mouse button. Release the mouse button and select the context menu command Fill in by month (fig. 108.1).

In some situations, you need to enter two dates before using the autocomplete feature. For example, if you need to enter dates that are consecutive Mondays, you must enter the first two dates in the series. Then select both dates, right-click and select Fill by days from the context menu.

Using formulas

Another way to enter a series of dates is using formulas. The advantage of a formula (rather than an autocomplete function) is that you can change the first date and all the rest will update automatically. Enter start date into a cell, and then use formulas (copying down the column) to create additional dates. For the following examples, suppose you entered the first date in the series in cell A1 and the formula in cell A2. You can then copy this formula down the column as many times as necessary.

To generate a series of dates separated by seven days, use this formula: \u003d A1 + 7.

To create a series of dates separated by one month, use the following formula: \u003d DATE (YEAR (A1); MONTH (A1) +1; DAY (A1))

To create a series of dates separated by one year, use this formula: \u003d DATE (YEAR (A1) +1; MONTH (A1); DAY (A1)

Use the following formula to create a series that includes only weekdays (all days except Saturday and Sunday). It assumes that the date in cell A1 is not a weekend: \u003d IF (WEEKDAY (A1) \u003d 6; A1 + 3; A1 + 1). This formula returns the serial number of the date, so you need to format the cells to display dates.



We recommend reading