Excel auto date formula, date format and to text, Entering 3-1 is prohibited to date
You can write a formula first and then drag to automatically generate the date in Excel. There are many date formats, numbers with dashes to numbers with letters, you can also custom. You can convert date to text and text to date, where converting date to text requires function, and converting text to date is simpler. Also, entering "number-number" in a cell is automatically converted to a date, but there is a way to prohibit it.
Chapters:
0:00 Excel automatic date change formula
0:40 Excel date format change
1:05 Excel Custom date format
1:26 Excel date to text
1:44 Excel text to date
2:05 Entering number - number (such as 3-1) is prohibited to convert to date
Description:
Excel auto date formula, date format and to text, Entering 3-1 is prohibited to date
One. Excel automatic date change formula (Excel auto date formula)
1. Click the first cell (A1), enter the date 3/29/2023, then click cell A2, enter the formula =Date(year(a1),month(a1),day(a1)+1), move the pointer to the small square in the lower right corner of cell A2, hold down the left button and drag down, release the left button to the last date you need, and the automatic generation of the date is complete.
2. Formula explanation:
You must enter = before the formula; A1 indicates the cell corresponding to the first row of the first column; Date() is a function of date; year(a1) indicates the year in which the date of cell a1 was obtained (i.e. 2023); month(a1) indicates the month in which a1 was obtained (i.e. 3); day(a1) + 1 means to take the day of a1 (that is, 29), and then add 1; the a1 cell cannot be empty and is a valid date.
Two. Excel date format change
1. Right-click cell A1, select "Format Cells" in the pop-up menu, open this window, select the "Date" under "Category", the date format is listed on the right, select "*March 14.2012", then the date format of current cell is converted to the selected format.
2. "Custom" is used to define formats that Excel does not provide, such as the yyyy-m-d format defined above; In addition, there are many date formats, some are displayed in English, and some display both date and time, so you can choose according to your needs.
Three. Excel date to text and text to date
(1) Excel date to text
1. Convert date to text with the Text(value, format_text) function in Excel, for example, convert the date of cell A1 to text format and fill the value into B1, just enter the formula =Text(A1, "m-d-yyyy" in B1). Press "Enter" on your keyboard and the date of A1 is converted to text.
2. When converting the date to text , you can customize a format, that is, custom format_text, such as the above m-d-yyyy is the custom format.
(2) Excel text to date
1. Right-click the cell with the date in text format, select "Format Cells" in the pop-up menu, open the window, select "Date" for "Category" on the left, and select a date format on the right.
2. Click "OK", and the date in text format is converted to date format (sometimes it may take a while to change to date format).
Four. Entering number - number (such as 3-1) is prohibited to convert to date
1. Entering "number-number" in a cell will be automatically converted to a date by default, for example, if you enter 3-1 in the first cell, press "Enter" on your keyboard, it will be automatically converted to "1-Mar".
2. For this case, just set the cell format to "text" before entering 3-1, they will not automatically convert to date, the setting method is: right-click the cell you want to enter text, select "Format Cells" → "Text under "Category" → "OK", and then enter 3-1, there is no automatic conversion to date.
Watch video Excel auto date formula, date format and to text, Entering 3-1 is prohibited to date online without registration, duration hours minute second in high quality. This video was added by user Loinsure 30 March 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 21,002 once and liked it 76 people.