Excel date to day number

DAYS function

This article describes the formula syntax and usage of the DAYS function in Microsoft Excel. For information about the DAY function, see DAY function.

Description

Returns the number of days between two dates.

Syntax

The DAYS function syntax has the following arguments.

End_date Required. Start_date and End_date are the two dates between which you want to know the number of days.

Start_date Required. Start_date and End_date are the two dates between which you want to know the number of days.

Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, Jan 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900.

Remarks

If both date arguments are numbers, DAYS uses EndDate–StartDate to calculate the number of days in between both dates.

If either one of the date arguments is text, that argument is treated as DATEVALUE(date_text) and returns an integer date instead of a time component.

If date arguments are numeric values that fall outside the range of valid dates, DAYS returns the #NUM! error value.

If date arguments are strings that cannot be parsed as valid dates, DAYS returns the #VALUE! error value.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Finds the number of days between the end date (15-MAR-2021) and start date (1-FEB-2021). When you enter a date directly in the function, you need to enclose it in quotation marks. Result is 42.

Finds the number of days between the end date in A2 and the start date in A3 (364).

Источник

DAY function

This article describes the formula syntax and usage of the DAY function in Microsoft Excel. For information about the DAYS function, see DAYS function.

Description

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

Syntax

The DAY function syntax has the following arguments:

Serial_number Required. The date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Remarks

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Источник

Get day name from date

Related functions

Summary

To get the day name (i.e. Monday, Tuesday,Wednesday, etc.) from a date, you can use the TEXT function. In the example shown, the formula in cell C5 is:

With January 1, 2000 in cell B5, the TEXT function returns the text string «Saturday».

Generic formula

Explanation

In this example, the goal is to get the day name (i.e. Monday, Tuesday, Wednesday, etc.) from a given date. There are several ways to go about this in Excel, depending on your needs. This article explains three approaches:

1. Display date with a custom number format
2. Convert date to day name with TEXT function
3. Convert date to day name with CHOOSE function

For all examples, keep in mind that Excel dates are large serial numbers, displayed as dates with number formatting.

Day name with custom number format

To display a date using only the day name, you don’t need a formula; you can just use a custom number format. Select the date, and use the shortcut Control + 1 to open Format cells. Then select Number > Custom, and enter one of these custom formats:

Excel will display only the day name, but it will leave the date intact. If you want to display both the date and the day name in different columns, one option is to use a formula to pick up a date from another cell, and change the number format to show only the day name. For example, in the worksheet shown, cell F5 contains the date January 1, 2000. The formula in G5, copied down, is:

Cells G5 and G6 have the number format «dddd» applied, and cells in G7:G9 have the number format «ddd» applied.

Day name with TEXT function

To convert a date to a text value like «Saturday», you can use the TEXT function. The TEXT function is a general function that can be used to convert numbers of all kinds into text values with formatting, including date formats. For example, with the date January 1, 2000 in cell A1, you can use TEXT like this:

In the worksheet shown, the goal is to display the day name only, so we use a custom number format like «ddd» or «dddd»:

Note: The TEXT function converts a date to a text value using the supplied number format. The date is lost in the conversion and only the text for the day name remains.

Day name with CHOOSE function

For maximum flexibility, you can create your own day names with the CHOOSE function. CHOOSE is a general-purpose function for returning a value based on a numeric index. For example, you can use CHOOSE to return one of three colors with a number like this:

In this example, the goal is to return a day name from a date, so we need to configure CHOOSE to select one of seven-day names. For example, the formula below would return «Wed» based on a numeric index of 4:

The challenge in this case is to get the right index for a date and for that we need the WEEKDAY function. For any given date, WEEKDAY returns a number between 1-7, which corresponds to the day of the week. By default, WEEKDAY returns 1 for Sunday, 2 for Monday, 3 for Tuesday, etc. In the worksheet shown, the formula in C12 is:

WEEKDAY returns a number between 1-7, and CHOOSE will use this number to select the corresponding value in the list. Since the date in B12 is January 1, 2000, WEEKDAY returns 7, and CHOOSE returns «Sat».

CHOOSE is more work to set up, but it is also more flexible, since it allows you to convert a date to a day name using any values you want. For example, you can use custom abbreviations or even abbreviations in a different language. In cell C15, CHOOSE is set up to use one letter abbreviations that correspond to Spanish day names:

Empty cells

If you use the formulas above on an empty cell, you’ll get «Saturday» as the result. This happens because an empty cell is evaluated as zero, and zero in the Excel date system is the date «0-Jan-1900», which is a Saturday. To work around this issue, you can use the IF function to return an empty string («») for empty cells. For example, if cell A1 may or may not contain a date, you can use IF like this:

The literal translation of this formula is: If A1 is not empty, return the TEXT formula, otherwise return an empty string («»).

Источник

Date and time functions (reference)

To get detailed information about a function, click its name in the first column.

Note: Version markers indicate the version of Excel a function was introduced. These functions aren’t available in earlier versions. For example, a version marker of 2013 indicates that this function is available in Excel 2013 and all later versions.

Returns the serial number of a particular date

Calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.

Converts a date in the form of text to a serial number

Converts a serial number to a day of the month

DAYS function

Returns the number of days between two dates

Calculates the number of days between two dates based on a 360-day year

Returns the serial number of the date that is the indicated number of months before or after the start date

Returns the serial number of the last day of the month before or after a specified number of months

Converts a serial number to an hour

ISOWEEKNUM function

Returns the number of the ISO week number of the year for a given date

Converts a serial number to a minute

Converts a serial number to a month

Returns the number of whole workdays between two dates

NETWORKDAYS.INTL function

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

Returns the serial number of the current date and time

Converts a serial number to a second

Returns the serial number of a particular time

Converts a time in the form of text to a serial number

Returns the serial number of today’s date

Converts a serial number to a day of the week

Converts a serial number to a number representing where the week falls numerically with a year

Returns the serial number of the date before or after a specified number of workdays

WORKDAY.INTL function

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

Converts a serial number to a year

Returns the year fraction representing the number of whole days between start_date and end_date

Important: The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

Источник

ДЕНЬ (функция ДЕНЬ)

В этой статье описаны синтаксис формулы и использование функции ДЕНЬ в Microsoft Excel. Чтобы узнать больше о функции ДНИ, см. статью ДНИ (функция ДНИ).

Описание

Возвращает день даты, заданной в числовом формате. День возвращается как целое число в диапазоне от 1 до 31.

Синтаксис

Аргументы функции ДЕНЬ описаны ниже.

Дата_в_числовом_формате — обязательный аргумент. Дата, которую необходимо найти. Даты вводятся с использованием функции ДАТА или как результат других формул и функций. Например, для указания даты 23 мая 2008 года следует воспользоваться выражением ДАТА(2008;5;23). Если даты вводятся как текст, это может привести к возникновению проблем.

Замечания

В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.

Значения, возвращаемые функциями ГОД, МЕСЯЦ и ДЕНЬ, соответствуют григорианскому календарю независимо от формата отображения для указанного значения даты. Например, если для формата отображения заданной даты используется календарь Хиджра, то значения, возвращаемые функциями ГОД, МЕСЯЦ и ДЕНЬ, будут представлять эквивалентную дату по григорианскому календарю.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник