How to automatically generate and format horizontal bars in Excel table dates

Key points of this article

Continuous dates are generated by adding a starting value and a fill handle, incrementing by day, which is faster than manually inputting one by one, and it is not easy to miss days.
The horizontal bar format is set with the custom format yyyy-mm-dd. Only the display is changed. The date value in the cell remains unchanged, and sorting and calculation are not affected.
There are two requirements for today's date: =TODAY() changes with the opening time, Ctrl+; enters a fixed value, select according to purpose.
Left-aligned "date" is usually text and cannot be sorted or subtracted. It needs to be converted to a real date value first and then formatted.

Manually typing dates in Excel is slow and error-prone: "2024/5/1" and "2024-05-01" are mixed in a column of data, and sorting, filtering, and statistics will be messed up. Many people want Excel to automatically generate a column of consecutive dates, or the current day's date to automatically appear every time they open the table, and they want to display it in a horizontal bar style like "2024-05-01".

This article is written for people who need to make daily reports, attendance sheets, schedules, and ledgers. After reading, you can use drag fill, formula and shortcut keys to generate dates, and set them in a horizontal bar format. You will also know why some dates look normal but cannot be used in calculations.

What we are talking about here is the date entry and display format in the cell. The date setting on the chart axis is another matter. The entry and judgment methods are different, which will be explained at the end of the article.

How to generate dates in Excel and unify them into horizontal bar format? One sentence conclusion

First use the starting date and the fill handle to generate continuous dates, and then use the custom format yyyy-mm-dd to display them in a horizontal bar style; use formulas for today's dates that need to be updated at any time, and use shortcut keys to enter today's dates that need to be fixedly recorded.

your situation	Recommended practices	reason
A sequence of consecutive dates is required, such as a month's schedule	Enter two adjacent dates and drag the fill handle	Intuitive operation, increasing every day
Need to increase regularly, such as every Monday, as long as the working day	Use a formula like =A1+7 or WORKDAY	The rules are written in the formula and can be updated as a whole by changing the starting point.
Every day the template is opened, the date of the day will be displayed.	Enter =TODAY()	Results vary with opening time
To record the date at a certain moment and keep it unchanged thereafter	Press Ctrl+; to enter	Get a fixed value that does not change with recalculation
The existing dates look correct, but they cannot be sorted.	Column or DATEVALUE conversion and then formatting	The text cannot be calculated. Just changing the appearance is useless.

Before you start: Confirm these points first

Confirm if it is a real date. After entering the correct date, the cells will be right aligned by default. The left-aligned "date" is mostly text and looks the same, but cannot be sorted or subtracted. This is the prerequisite for the success of subsequent operations.
Confirm how to write the starting value. "2024/5/1" and "2024-5-1" are usually recognized. If there are spaces, full-width characters or redundant text in between, recognition may fail.
Confirm if fixed value is required. The TODAY function changes over time. If the table is a historical record, using it directly will change the original date. Fixed values should be used in such scenarios.
Confirm the delimiter in the format. The "-" in the custom format is a literal symbol and will not be replaced by the system. Use it uniformly for stability. The display of day and month names is affected by the system locale.
Confirm data source. If the date comes from a PDF table, you can first export it to Excel in the PDF editor of the OmniDoc desktop client, and then process it for this article. After exporting, the dates must be checked one by one, and the conversion results require manual confirmation.

Steps to generate consecutive dates and format horizontal bars in Excel

Enter the starting date in cell A1, for example, 2024-05-01. Excel recognizes it as a date and right-aligns it, indicating that it is a calculable date value.
Enter the next day’s date 2024-05-02 in cell A2, and then select A1 and A2 at the same time. Two values let Excel know that the step size is one day.
Move the mouse to the fill handle in the lower right corner of the selection, hold down and drag down to the required number of lines, and release the mouse.
If the results are all on the same day, it means they are not filled in sequence. After releasing, click the pop-up fill option to change to fill in sequence.
Select the entire column of dates, press Ctrl+1 to bring up the cell formatting window, switch to the number-related page, and select a custom category.
Fill in yyyy-mm-dd in the format code input box and confirm saving. If you only need the month and day, you can fill in mm-dd. All dates will be displayed as horizontal bars, but the values themselves will not change.
Click on any date cell, look at the content in the edit column, and make sure it is still a date and not text, and then try to sort the column to make sure the order is correct.

How to set today’s date and time into a horizontal bar style

There are two common requirements for today's date, corresponding to two writing methods.

Today's date that can be updated at any time: Enter =TODAY() in the cell. It displays the date on the day the form is opened, and it will automatically change when it is opened the next day, making it suitable for templates. If you need tomorrow's date, you can write =TODAY()+1.
Fix the recorded date today: Select the cell and press Ctrl+; to enter. It is a static value that does not change with recalculations and is suitable for recording check-ins, check-ins, and progress dates.
Bar style for time: Times in Excel are usually separated by colons, for example, 08:30. If you want to display the date and time at the same time, such as "2024-05-01 08:30", you need to set the cell format to yyyy-mm-dd hh:mm. Press Ctrl+Shift+; to enter the current time, but it is also a fixed value.

It should be noted that the date and time must be stored in the same cell to be displayed together. If the date and time are listed in two columns, they must be formatted separately or combined using formulas.

Check each item after completion

Check item	What do you think?	What if there's a problem?
Whether the date is right-aligned	Look at the cell alignment	The left-aligned description is the text, which is reformatted after it is converted by column or DATEVALUE.
Is the filling increasing by day?	See if the number of days between the first and last dates is in line with the number of rows.	Use the fill option to fill in a sequence, or use the = A1room1 formula instead
Is the format of the horizontal bar uniform?	Scan the whole column to see if it is mixed with other writing methods	Check the entire column to reformat the custom format
Is the sort correct?	Sort the date column, see the order	Disordered instructions are mixed with text dates, and converted after finding out.
Whether the fixed value is mixed with the formula	Click on the cell to see if the edit bar is a function.	The parts that need to be fixed are selectively pasted to numerical values to avoid being refreshed.
Date exported from PDF	Compare it with the original file one by one	Manual correction of inconsistencies shall be based on the original document

# Core comparison

Method	Suitable for the scene	Whether it changes with time	After modifying the starting point
Fill handle drag	Generate consecutive dates at one time	No, it is a fixed value after generation.	Need to drag again
Date formula (e. G. = A1: 1, = TODAY ())	Template, rule increment, date of the day	Yes, it depends on the formula	Other dates automatically follow
Shortcut key entry (Ctrl+;)	Record the date of a moment	No	Need to modify one by one

When making a choice, you can think about two things first: whether the date needs to be updated automatically and whether it needs to be modified in batches later. If you need to update automatically, use the formula, and if you need to retain the history, use a fixed value.

# frequently asked questions (FAQ)

1. Why does it appear different after entering 2024-5-1?

This is usually the other format that the cell already has, and the Excel is displayed in that format. After selecting the cell, press Ctrl+1 to reset to yyyy-mm-dd to display uniformly.

2. What if the whole column is the same date after dragging the padding handle?

After releasing the mouse, click the pop-up fill option and select fill in sequence. You can also enter two adjacent dates and then select drag to let Excel recognize the step size.

3. The date is already in horizontal format, why can't it be sorted or calculated?

The appearance is in horizontal bar format, which does not mean that it is a date value. In most cases, it is text, which can be converted by using the DATEVALUE function in blank cells, or by using the sorting function to convert the text to a date, and then format the horizontal bar.

4. The date of the Abscissa of the chart is incorrect. Do you want to change it here?

I don't need it. The date interval and display of the chart axis belong to the chart settings, and the date entry and format in the cell are two different things. First confirm that the source data itself is the real date value, and then adjust the coordinate axis in the chart.

# Summary

Consecutive dates are generated with a starting value plus a padding handle, and the date of the day is selected according to whether or not you need to update the selection formula or shortcut key, and finally use the yyyy-mm-dd uniform horizontal bar format. When you are done, you can take a look at the alignment and sort results to confirm that the date is really available.

Open OmniDoc and process your form file