Date and time calculations in excel. Formatting Numbers as Date and Time Values

to consolidate in practice the work with the master of functions;
learn how to use the Date and Time functions.

Software: Ms Office: Ms Excel 2007

During the classes

1. Organizational part

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

2. Introductory briefing

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

What is a function?
How many functions are included in Excel, and into what categories are they distributed?
What are the ways to call the "Function Wizard" window?
Statistical functions and their syntax?

In the process of repetition, a presentation on the last lesson is displayed on the screen when studying the topic “Statistical functions”.

2.2. Inform the topic and goals of the new lesson;
2.3. Explanation of 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. Current briefing.

3.1. Issue Handout;
3.2. Target bypass;
3.3. Checking the correct organization of workplaces;
3.4. Checking the correctness of the implementation of techniques;
3.5. Working with those who are lagging behind;
3.6. Knowledge control.

4. Final briefing.

4.1. Conduct an analysis of the completed task;
4.2. Parse errors;
4.3. Report grades.

Summary of the introductory briefing

Date and time functions are used to convert date and time formats, calculate spans of time, and insert automatically updated date and time values into a worksheet. There are a total of 20 functions in the Date & 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, and so on. This article was written on 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 an ordinal 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 the given cell dates.

The maximum available date is December 31, 9999 (pre-Excel 97 is December 31, 2078).

Excel also stores time in the form of numbers, more precisely, in the form of decimal fractions of the numbers of the corresponding dates. That is, a fractional part corresponding to the part of the day is added to the serial number of the date. For example, March 06, 2011 has a sequence number of 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 (one second is 10,000 hours).

List of date and time functions

Function	Description
TIMEVALUE	Converts time from text format to a number representing the time in Excel.
TIME	Converts hours, minutes, and seconds given as numbers to a number in Excel code.
YEAR	Returns the year corresponding to the given date from 1900 to 9999.
DATE OF	Returns an integer representing a specific date.
DATEVALUE	Converts a date, which is stored as text, to a serial number, which Microsoft Excel takes it as a date.
DATAMES	Returns the serial number of the date that is a specified number of months ahead or back from the specified date.
DAY	Returns the day of a date given in numeric format.
DAYWEEK	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).
SHARE YEAR	Returns the percentage of the year, which is the number of days between two dates (start and end).
EOMONTH	Returns the sequence number last day of the month that is the specified number of months from the date specified in the "start_date" argument.
MONTH	Returns the month for a date given in numeric format.
MINUTES	Returns the minutes corresponding to the argument time_in_number_format.
NOMWEEK	Returns the week number for a given date.
WORKDAY	Returns a number that represents the date that is the specified number of working days ahead or behind the start date.
TODAY	Returns the current date in numeric format.
SECONDS	Returns the seconds corresponding to the argument time_in_number_format.
TODAY	Returns the current date and time in numeric format.
HOUR	Returns the hour corresponding to the given time.
NETWORKDAYS	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 the computer.

Current briefing

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

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

3. =YEAR (date_in_number_format), returns the year corresponding to the given date from 1900 to 9999;

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

5. =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 textual representation to a number format;

6. =DATE(start_date; number_of_months), the function returns the ordinal number of the date that is a specified number of months ahead or back from the specified date;

7. =DAY (date_as_number), - converts the date in numerical format to the day of the month (from 1 to 31);

8. =WEEKDAY(date_number_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 assigned the number 1;

9. =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 the calculation. In this example, no method was selected;

10. =YEARFRACTION(start_date; end_date; basis), the function returns the fraction of the year, which is the number of days between two dates. We apply a number format to the result;

11. =EOMONTH(start_date; number_of_months), the function returns the ordinal number of the last day of the month that is a specified number of months from the date specified in the "start_date" argument;

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

13. = MINUTES (time_in_number_format), returns minutes as a number between 0 and 59;

14. = NOMWEEK(date_number_date; return_type), the function returns the week number for a specific date;

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

16. = TODAY, the function returns the date in numerical format (this function has no arguments);

17. =SECONDS (time_in_number_format), the function returns seconds as a number from 0 to 59;

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

19. = HOUR (time_in_number_format), the function returns hours as a number from 0 to 23;

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

Practical work 1

In cells highlighted in green make the appropriate calculations using the “Date and Time” functions (DAYWEEK, NOMWEEK, WORKDAY);

Practical work 2

In the cells highlighted in blue, make the appropriate calculations using the “Date and Time” functions (YEAR, TODAY);

Practical work 3

In the cells highlighted in gray, make the appropriate calculations using the “Date and Time” functions .

Final briefing

Conduct an analysis of the completed task and knowledge control;
Parse errors;
Report grades.

Function DATE OF returns the ordinal number of a specific date.

Syntax: DATE(year, month, day)

The DATE function syntax has the following arguments.

Year is a 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, Microsoft Excel for Windows uses the 1900 date system, which means 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 between 0 (zero) and 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) of the year.

If the argument year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, the DATE(2008;1;2) function returns January 2, 2008.

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

Month is a required argument. A positive or negative integer between 1 (January) and 12 (December) representing the month of the year.

If the value of the argument 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 value of the argument month less than 1, argument month Subtracts the value of this number of months, plus 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 is a required argument. A positive or negative integer between 1 and 31 representing the day of the month.

If the value of the argument day greater 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 value of the argument day less than 1, argument day Subtracts the value of this number of days, plus 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 cell format.

Syntax: DATE(year, month, day)

For example, =DATE(C2,A2,B2) concatenates the year from cell C2, the month from cell A2, and the 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 . In addition, you can automatically populate a column with dates.

Changing the date format

The DATE function generates a date.

=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

The LEFT function gets 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 digits to the right of the 5th digit. 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 date of, specified for cell D2.

The RIGHT function gets 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 certain number of days, simply add it to the value or cell reference of the date, or subtract it.

In the example below, cell A5 contains a date that needs to be incremented or decremented 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 to it. This format is determined by the date and time regional settings set in the 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 the control panel settings.

Display numbers as dates or times

You can format date and time values as you type. For example, if you enter the value in a cell 2/2 , Excel will automatically interpret it as a date and display the date in the cell Feb 02. If this format is not suitable (for example, you need to display the date in a cell as February 2, 2009 or 02.02.09 ), you can select a different date format in the dialog box Cell Format, as described in the procedure below. Similarly, if you enter a value into a cell 9:30 am or 9:30 p.m., Excel will automatically interpret it as a time and display the time in the cell 9:30AM or 9:30PM. 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 instead of months.

Hours, minutes and seconds

To display	Use the 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 4 AM format
Time format "4:36 PM"
Time 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 the designation 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 for users who haven't done it before. For more information about creating custom number formats, see Create and delete custom number formats.

To quickly apply the default date or time format, click the cell containing the date or time, and then 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 a column containing cell data, double-click its right border. The column width will be automatically adjusted to reflect the contents of the cells. You can also drag the right border of the column to the desired width.

If you try to override the date or time format by selecting General on the list Numeric formats, Excel displays the numeric code. If you enter the date or time again, the default date or time format will be used. To enter a date or time in a special format, such as January 2010, you can format the value as text by selecting Text on the list Numeric formats.

To quickly enter the current date, select any empty cell on the worksheet and press CTRL+; (semicolon), and then, if necessary, press Enter. To insert a date that is updated to the current date each time the worksheet is opened or the formula is recalculated, type =TODAY() in an empty cell and press Enter.