Date value in excel. Formatting numbers as date and time values

Function THE DATE returns the ordinal of a specific date.

Syntax: DATE (year; month; day)

The arguments for the DATE function are described below.

Year - required argument. Argument value year can contain from one to four digits. Excel interprets the argument year according to the date system used on your computer. By default in Microsoft Excel for Windows, the date system is 1900, that is, January 1, 1900 is considered the first date.

Advice: To avoid unexpected results, use as the argument value year four digits. For example, the value "07" can be interpreted as "1907" or "2007". The four-digit year designation prevents confusion.

• If the argument year is in the range from 0 (zero) to 1899 (inclusive), Excel adds this value to 1900 to calculate the year. For example, the DATE (108; 1; 2) function returns January 2, 2008 (1900 + 108).

  If the argument year is in the range 1900 through 9999 (inclusive), Excel uses this value as the year. For example, the DATE (2008; 1; 2) function returns January 2, 2008.

  If the argument value year less than 0 or greater than 9999, Microsoft Excel returns the #NUM! error value.

Month - required argument. A positive or negative integer ranging from 1 (January) to 12 (December) representing the month of the year.

• If the argument value month greater than 12, argument month adds the number of months to the first month of the specified year. For example, the DATE (2008; 14; 2) function returns the number corresponding to February 2, 2009.

  If the argument value month less than 1, argument month subtracts the value of that number of months, increased by 1, from the first month of the specified year. For example, the DATE (2008; -3; 2) function returns the number corresponding to September 2, 2007.

Day - required argument. A positive or negative integer in the range 1 through 31 representing the day of the month.

• If the argument value day more than the number of days in the specified month, argument day adds this number of days to the first day of the month. For example, the DATE (2008; 1; 35) function returns the number corresponding to February 4, 2008.

  If the argument value day less than 1, argument day subtracts the value of that number of days, increased by 1, from the first day of the specified month. For example, the DATE (2008; 1; -15) function returns the number corresponding to December 16, 2007.

Note: IN Excel dates are stored as serial numbers, which allows them to be used in calculations. The date January 1, 1900 corresponds to the number 1, and January 1, 2008 - 39448, since the interval between these dates is 39 447 days. To display the correct date, you need to change the format of the cell.

Syntax: DATE (year; month; day)

For instance, \u003d DATE (C2; A2; B2) concatenates the year value from cell C2, month from cell A2, and day from cell B2, and puts them in one cell as a date. The example below shows the result in cell D2.

Need to insert dates without using a formula? No problem! You can insert the current or updated date. You can also automatically populate the column with dates.

Changing the date format

The DATE function generates a date.

\u003d DATE (LEFT (C2,4); MID (C2,5,2); RIGHT (C2,2))

The LEFT function receives the first 4 characters from the left in cell C2. This makes the value "2014" the year of the converted date in cell D2.

The MID function refers to cell C2. She gets 2 characters to the right of the 5th character. This makes the value "03" the month of the converted date in cell D2. "0" is not included in the result due to the format the datespecified for cell D2.

The RIGHT function takes the first 2 characters in cell C2 from the right edge and adds "14" to cell D2 as the day value.

Increase or decrease the date by the specified number of days

To increase or decrease a date by a specified number of days, simply add it to the value or cell reference with the date, or subtract it.

In the example below, cell A5 contains the date that needs to be increased or decreased by 7 days (value in cell C5).

When you enter a date or time in a cell, the default date and time format is applied. This format is determined by the regional date and time settings in Windows Control Panel and changes when these settings are changed. You can use other formats to display the date and time, most of which are not affected by control panel settings.

In this article

Display numbers as date or time

You can format date and time values \u200b\u200bas you type. For example, if you enter the value in a cell 2/2 Excel automatically interprets it as a date and displays the date in the cell. 02.Feb... If this format does not work (for example, you need to display the date in a cell as February 2, 2009 or 02.02.09 ), you can choose a different date format in the dialog box Cell formatas described in the procedure below. Similarly, if you enter the value into the cell 9:30 a or 9:30 pExcel automatically interprets it as time and displays the time in the cell. 9:30 AM or 9:30 PM... The time display format can also be configured in the dialog box Cell format.

Create a custom date or time format

If "m" appears immediately after the "h" or "hh" code, or immediately before the "ss" code, Excel displays minutes, not months.

Hours, minutes and seconds

To display	Use code
Hours as numbers from 0 to 23
Hours as numbers from 00 to 23
Minutes as numbers from 0 to 59
Minutes as numbers from 00 to 59
Seconds as numbers from 0 to 59
Seconds as numbers from 00 to 59
Hours in the "4 AM" format
Time in the format "4:36 PM"
Time in the format "4:36:03 P"	H: mm: ss A / P
Elapsed time in hours; for example 25:02
Elapsed time in minutes; for example 63:46
Elapsed time in seconds
Fraction of seconds

AM and PM ... If the format contains AM or PM, the 12-hour system is used, where "AM" or "A" indicates the time of day from midnight to noon and "PM" or “P” indicates the time of day from noon to midnight. Otherwise, the 24-hour system is used. The “m” or “mm” code must appear immediately after the “h” or “hh” code, or immediately before the “ss” code, otherwise Excel displays the month, not the minutes.

Creating custom number formats can seem like a daunting task to users who haven't done it before. For more information on creating custom number formats, see the article Create and remove custom number formats.

To quickly apply the default date or time format, click the cell containing the date or time and press CTRL + SHIFT + # or [email protected]

If, after applying a date or time format to a cell, it displays characters ##### the cell is probably not wide enough to display the data. To increase the width of the column containing the cell data, double-click its right border. The column width will be automatically resized to reflect the contents of the cells. You can also drag the right column border to the desired width.

If you try to cancel the application of the date or time format by choosing the General in the list Number formats, Excel displays the numeric code. If you enter the date or time again, the default date or time format is used. To enter a date or time in a special format such as 2010 january minutes, you can format the value as text by selecting Text in the list Number formats.

To quickly enter the current date, select any blank cell in the worksheet and press CTRL +; (semicolon) and then, if necessary, press Enter. To insert a date that will update to the current date whenever you open a worksheet or recalculate a formula, type \u003d TODAY () in a blank cell and press Enter.

additional information

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

• to consolidate in practice the work with the function wizard;
• learn to use the Date and Time function.

Software: Ms Office: Ms Excel 2007

During the classes

1. Organizational part

1.1. Check those present at the lesson;
1.2. Check the readiness of students for the lesson.

2. Introductory briefing

2.1. Checking the knowledge of the passed material (frontal survey);

1. What is a function?
2. How many functions are included in Excel, and what categories are they categorized into?
3. What are the ways to open the "Function Wizards" window?
4. Statistical functions and their syntax?

In the process of repetition, the screen displays a presentation on the previous lesson while studying the topic “Statistical functions”.

2.2. Report the topic and goals of the new lesson;
2.3. Explanation of the new material;

2.3.1. Explain and show with a projector the syntax of the Date and Time functions;

2.4. Repetition of labor protection requirements.

3. Ongoing briefing.

3.1. Distribute handouts;
3.2. Targeted bypass;
3.3. Checking the correct organization of workplaces;
3.4. Checking the correctness of the techniques;
3.5. Working with laggards;
3.6. Knowledge control.

4. Final briefing.

4.1. Analyze the completed assignment;
4.2. Parse errors;
4.3. Report estimates.

Introductory briefing summary

Use date and time functions to convert date and time formats, calculate time ranges, and insert automatically updated date and time values \u200b\u200binto a worksheet. There are a total of 20 functions in the "Date and Time" category.

Excel stores dates as numbers from 1 to 2,958,465 (up to 65,380 in versions earlier than Excel 97). These numbers are the "number in order" of the given day, counted from January 1, 1900. Ordinal number 1 corresponds to January 1, 1900, number 2 is January 2, 1900, etc. The date of this article was October 20, 2012. has serial number 41202.

This system allows you to use dates in formulas. For example, it can be used to easily calculate the number of days between two dates. Surprisingly, but in excel program there is a date with a serial number equal to zero - January 0, 1900. This non-existent date is used to represent temporary data that is not associated with any particular date.

To display a numeric value as a date, simply apply the format to a given cell dates.

The maximum date available is December 31, 9999 (in versions prior to Excel 97, December 31, 2078).

Excel stores time also in the form of numbers, more precisely, in the form of decimal fractions of the numbers of the corresponding dates. That is, the fractional part corresponding to the part of the day is added to the serial number of the date. For example, March 06, 2011 has the serial number 40608, and noon on March 06, 2011 (12:00) will correspond to the number 40608.5 since 12 hours is 0.5 of 24.

The maximum time value that can be entered in a cell (without a date value) is 9999: 59: 59 (10 thousand hours less one second).

List of date and time functions

Function	Description
TIME VALUE	Converts time from text format to a number representing the time in Excel.
TIME	Converts the hours, minutes, and seconds specified as numbers to a number as Excel code.
YEAR	Returns the year corresponding to the specified date from 1900 to 9999.
THE DATE	Returns an integer representing a specific date.
DATEVALUE	Converts a date, which is stored as text, to a sequence number that Microsoft Excel interprets as a date.
DATE	Returns the ordinal of a date that is a specified number of months forward or backward from a specified date.
DAY	Returns the day of the date specified in numeric format.
DAY	Returns the day of the week corresponding to the date.
DAYS360	Returns the number of days between two dates based on a 360-day year (twelve months of 30 days).
LENGTH	Returns the fraction of a year, which is the number of days between two dates (start and end).
OF THE EONTHS	Returns a sequential number last day the month that is the specified number of months from the date specified in the start_date argument.
MONTH	Returns the month for a numeric date.
Minutes	Returns the minutes corresponding to the time_in_numeric_format argument.
NUMBER OF WEEKS	Returns the week number for a specific date.
WORKDAY	Returns a number that represents a date that is a specified number of business days forward or backward from the start date.
TODAY	Returns the current date in numeric format.
SECONDS	Returns the seconds corresponding to time_in_numeric_format.
Tdata	Returns the current date and time in numeric format.
HOUR	Returns the hour corresponding to the specified time.
CLEANING DAYS	Returns the number of business days between start_date and end_date. Holidays and weekends are not included in this number.

To consolidate the new material, students are invited to complete practical tasks on a computer.

Current briefing

1. \u003d TIMEVALUE (time_as_text) , converts the time from text format to number. After entering the formula, set the format “Time” for the cells;

2. =TIME(hour; minute; second), the function converts the hours, minutes and seconds specified in the form of numbers into a number in the form of Excel code;

3. \u003d YEAR (date_in_numeric_format), returns the year corresponding to the given date from 1900 to 9999;

4. \u003d DATE (year; month; day), returns an integer representing a specific date;

5. \u003d DATEVALUE (date_as_text), the function returns the numeric format of the date represented as text. The DATEVALUE function is used to convert a date from a textual representation to a number format;

6. \u003d EEC DATE (start_date; number of months), the function returns the ordinal number of the date, which is a given number of months forward or backward from a given date;

7. \u003d DAY (date_as_number), - converts a date in numeric format to a day of the month (from 1 to 31);

8. \u003d WEEKDAY (date_num_format; type), the function returns a number from 1 to 7, corresponding to the number of the day of the week for the given date. In this example, select “Type” 2, i.e. Monday is numbered 1;

9. \u003d DAYS360 (start_date; end_date; method ), the function calculates the number of days between two dates based on a 360 day year (12 months x 30 days).

Method is a boolean value that determines which method, European or American, should be used in calculations. In this example, no method was selected;

10. \u003d LENGTH (start_date; end_date; basis), the function returns the fraction of the year, which is the number of days between two dates. Apply the number format to the result;

11. \u003d EON MONTHS (start_date; number_months), the function returns the ordinal of the last day of the month, which is the specified number of months from the date specified in the "start_date" argument;

12. \u003d MONTH (date_in_numeric_format ), the function returns the month - a number from 1 (January) to 12 (December);

13. \u003d MINUTES (time_in_numeric_format), returns minutes as a number between 0 and 59;

14. \u003d WEEK NUMBERS (date_number; return_type), the function returns the week number for a specific date;

15. \u003d WORKDAY (start_date; number_days; holidays), the function returns a number that represents a date that is a given number of working days ahead or back from the start date;

16. \u003d TODAY, the function returns the date in numeric format (this function has no arguments);

17. \u003d SECONDS (time_in_numeric_format), the function returns seconds as a number from 0 to 59;

18. \u003d THAT, the function returns the current date and time in numeric format (this function has no arguments);

19. \u003d HOUR (time_in_numeric_format), the function returns hours as a number from 0 to 23;

20. \u003d NETWORKDAYS (start_date; end_date; holidays), the function returns the number of full business days between two dates;

Practical work 1

In cells selected green make the appropriate calculations using the "Dates and Time" functions (WEEKDAY, WEEKDAY, WORKDAY);

Practical work 2

In the cells highlighted in blue, perform the appropriate calculations using the functions "Date and Time" (YEAR, TODAY);

Practical work 3

In the cells highlighted in gray, perform the appropriate calculations using the "Date and Time" functions .

Final briefing

1. Analyze the completed assignment and control knowledge;
2. Parse errors;
3. Report estimates.