Date and Time Functions – A to M

1. How to use the DATE function

What is the DATE function?

The DATE function returns a number that acts as a date in the Excel environment.

1. Introduction

What is an Excel date?

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

2. DATE Function Syntax

DATE(year, month, day)

3. DATE Function Arguments

year	A number equal or higher than 1900.
month	A number between 1 (January) and 12 (December). You can use larger numbers as well.
day	A number between 1 and 31 based on the number of days in the month specified in argument month.

Back to top

4. DATE Function Example

The image above shows you the DATE function in column E. It uses three arguments, the first argument is the year found in column B.

The second argument is the month, shown in column C. Note that you need to use a number for the month.

The third argument is the day of the month, displayed in column D. Make sure you enter the correct day for the month, keep in mind most months have 30 days or 31 days. February has 28 days except for leap years that have 29 days.

5. Add or subtract months and days

If you use a month number larger than 12, for example 13, DATE(2009,13,1) the DATE function returns 1/1/2010. Month number zero returns the previous month.

The same thing happens with a day number that is larger than 31 or smaller than 1.

You can use that to do dynamic date series or do date-based calculations.

Back to top

6. Date function example

The image above demonstrates the date function in column E, it uses values from column B, C and D.

Formula in cell F3:

Back to top

7. What does the Date function do?

The DATE function returns an Excel date that you can use in formulas, Excel can't calculate dates in formulas unless you reference a cell containing an Excel date or create an Excel date using the DATE function.

Hardcoding dates in a formula are not possible, use the DATE function to create an Excel date.

Why is that?
Excel uses numbers formatted as dates, 1 is 1/1/1900, and 1/1/2000 is 36526.

Prove it?
Sure, type 1 in a cell and press Enter. Select the cell and press CTRL + 1 to format the cell.

A dialog box appears, select Date in Category: and press the "OK" button.

Number 1 is now formatted as an Excel date, the cell shows 1/1/1900.

Back to top

8. Date function format

The animated image above shows you the "Format Cells" dialog box, it allows you to choose from many different date formatting types. Here is how:

1. Select the cell.
2. Press CTRL + 1 to open the "Format Cells" dialog box.
3. Select Date in the Category:, see the image above.
4. Pick a formatting type.
5. Press with left mouse button on "OK" button to apply changes.

You can also use the TEXT function to format dates.

Formula in cell B2:

Back to top

Explaining formula in cell B2

Step 1 - Create an Excel date

The DATE function calculates a serial number that represents a specific date based on three arguments. Year, month, and day.

DATE(year, month, day)

DATE(1900, 1, 1)

returns 1.

Step 2 - Return a number formatted as a date

The TEXT function formats a cell value based on given placeholder characters.

TEXT(value, format_text)

TEXT(DATE(1900, 1, 1), "d-m-yyyy")

becomes

TEXT(1, "d-m-yyyy")

and returns 1-1-1900.

Back to top

9. Date function not working

1. Did you spell the DATE function correctly?
   
   Excel returns a #NAME? error if you didn't.
2. Check the arguments, they must be numerical. Text is not allowed.
   
   Excel returns a #VALUE! error if any of the arguments contain a text value.
   year - a number equal or larger than 0 (zero)
   month - any number positive or negative
   day - any number positive or negative
3. Did you use exactly three arguments?
   
   Excel returns a dialog box: You've entered too many arguments for this function.
   DATE(year, month, day)
4. Excel Date function returns the wrong date.
   
   If you try a year before 1900 Excel returns a date that you probably didn't expect. Here is how Excel came up with that date. 1900+1800 = 1/1/3700
5. The DATE function returns hashtags.
   
   The DATE function can't return dates before 1/1/1900. The formula in cell B2 above tries to calculate an Excel date before 1/1/1900.

Back to top

10. How to make Excel order by date

The image above shows a list of dates in column B, here is how to sort the dates.

1. Press with right mouse button on any date in column B.
   A popup menu appears.
   
2. Press "Sort". Another popup menu appears.
3. Press either "Sort Oldest to Newest" or "Sort Newest to Oldest".

Here is how to sort the dates using a formula.

Formula in cell D2:

Back to top

Explaining formula in cell D2

Step 1 - Create a sequence of values from 1 to n

The ROWS function returns a number based on the number of rows in a cell reference.

ROWS(ref)

ROWS($A$1:A1)

returns 1.

Step 2 - Extract k-th largest serial number

The LARGE function calculates the k-th largest number.

LARGE(array, k)

LARGE($B$2:$B$17, ROWS($A$1:A1))

becomes

LARGE($B$2:$B$17, 1)

becomes

LARGE({45338; 53253; 49446; 51878; 48237; 52238; 44437; 53971; 49050; 47745; 45727; 45027; 49156; 49329; 48599; 49590}, 1)

and returns 53971 (10/6/2047) in cell D2.

Back to top

11. Date function add days

The formula in cell C3 adds 10/25/2024 with 10 days specified in cell B3.

Formula in cell C3:

Back to top

Explaining formula in cell C3

Step 1 - Add dates

The plus sign lets you add numbers in Excel formulas.

25+B3

becomes

25 + 10 equals 35.

Step 2 - Calculate serial number representing an Excel date

The DATE function calculates a serial number that represents a specific date based on three arguments. Year, month, and day.

DATE(year, month, day)

DATE(2024,10,25+B3)

becomes

DATE(2024,10,35)

and returns 45600 formatted as 11/4/2024 in cell C3.

Back to top

12. Date function subtract days

The formula in cell C3 subtracts 10/25/2024 with 10 days specified in cell B3.

Formula in cell C3:

Back to top

Explaining formula in cell C3

Step 1 - Subtract dates

The minus sign lets you subtract numbers in Excel formulas.

25-B3

becomes

25 - 10 equals 15.

Step 2 - Calculate serial number representing an Excel date

The DATE function calculates a serial number that represents a specific date based on three arguments. Year, month, and day.

DATE(year, month, day)

DATE(2024,10,25-B3)

becomes

DATE(2024,10,15)

and returns 45580 formatted as 11/4/2024 in cell C3.

Back to top

13. Date function add months

The formula in cell E3 adds months based on the number in cell B3 to a date specified in cell C3.

Formula in cell C3:

Back to top

Explaining formula in cell C3

Step 1 - Calculate year

The YEAR function returns a number representing the year based on an Excel date (serial number).

YEAR(serial_number)

YEAR(C3)

becomes

YEAR(1/14/2024)

becomes

YEAR(45305)

and returns 2024.

Step 2 - Calculate month

The MONTH function returns a number representing a month. 1 - January, ..., 12 - December.

MONTH(serial_number)

MONTH(C3)+B3

becomes

MONTH(45305)+10

becomes

1+10 equals 11.

Step 3 - Calculate day

The DAY function returns a number representing the day from an Excel date (serial number).

DAY(C3)

becomes

DAY(45305)

and returns 14.

Step 4 - Calculate date

The DATE function calculates a serial number that represents a specific date based on three arguments. Year, month, and day.

DATE(year, month, day)

DATE(YEAR(C3), MONTH(C3)+B3, DAY(C3))

becomes

DATE(2014, 11, 14)

and returns 45610 (11/14/2024).

Back to top

14. Last day of a given month

The formula in cell D3 calculates the last day for a given month based on a date in cell B3. The date is 1/1/2024, the last day in January 2024 is 31.

The way this works is that the formula adds one month to the date in cell B3 then subtracts with 1 to get the last date of the previous month.

Formula in cell C3:

Back to top

Explaining formula in cell C3

Step 1 - Calculate year

The YEAR function returns a number representing the year based on an Excel date (serial number).

YEAR(serial_number)

YEAR(B3)

becomes

YEAR(1/1/2024)

becomes

YEAR(45292)

and returns 2024.

Step 2 - Calculate month

The MONTH function returns a number representing a month. 1 - January, ..., 12 - December.

MONTH(serial_number)

MONTH(B3)+1

becomes

MONTH(45292)+1

becomes

1+1 equals 2.

Step 3 - Calculate day

The DAY function returns a number representing the day from an Excel date (serial number).

DAY(B3)

becomes

DAY(45292)

and returns 1.

Step 4 - Calculate the date

The DATE function calculates a serial number that represents a specific date based on three arguments. Year, month, and day.

DATE(year, month, day)

DATE(YEAR(B3),MONTH(B3)+1,1)-1

becomes

DATE(2014, 2, 1)-1

becomes

45323-1

and returns 45322 (1/31/2024).

Back to top

15. Get Excel file

Back to top

16. Calculate date given weekday and week number (week starts on Sunday)

This section demonstrates formulas that returns a date based on a week number and a weekday like Sun to Sat. It uses the current year to calculate the date.

17. Calculate date given weekday and week number (week starts on Monday)

This example demonstrates a formula that works with weeks that begin with Mondays.

Formula in cell 5:

Note that the WEEKDAY function uses 2 in the second argument meaning the week begins with a Monday and the array in the second argument in the MATCH function begins with Monday.

Back to top

Back to top

Tip! You can shrink the formula if you can convert the weekday to a number yourself.

2. How to use the DATEDIF function

What is the DATEDIF function?

The DATEDIF function returns the number of days, or months, or years between two dates. The DATEDIF function exists in order to ensure compatibility with Louts 1-2-3.

DATEDIF function not found?

You may think that the DATEDIF function is not in Excel when you type the function in the formula bar. The DATEDIF function is not missing, however, it doesn't show up in the list.

Type the DATEDIF function, the beginning parentheses, the arguments, the closing parentheses, and it will work.

What are dates in Excel?

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

1. DATEDIF function Syntax

DATEDIF(start_date,end_date,unit)

Note, this function shows no arguments in the formula bar.

2. DATEDIF function Arguments

start_date	Required. The beginning date of the range you want to calculate.
end_date	Required. The ending date of the period.
unit	Required. This determines what the function returns. See table below.

Unit argument

Unit	Output
"Y"	Complete years in the range.
"M"	Complete months in the range.
"D"	Days in the range.
"MD"	Months and years of the dates are ignored. Don't use this argument, may return incorrect results.
"YM"	Returns months. Days and years of the date arguments are ignored.
"YD"	Returns days. The years of the date arguments are ignored.

3. DATEDIF example - calculate complete years between two dates

This example demonstrates how to calculate complete years between two given dates using the DATEDIF function. The first date is specified in cell B3 and the second date in cell C3.

Formula in cell D3:

The third argument determines the unit to calculate, in this case, it is "Y" which stands for year. Excel returns 2 in cell D3 meaning there are two complete years between "1/4/2013" and "1/6/2015".

4. DATEDIF example - calculate days between two dates

This example shows how to calculate days between two given dates using the DATEDIF function. The first date is specified in cell B4 and the second date in cell C4.

Formula in cell D4:

The third argument determines the unit to calculate, in this case, it is "D" which stands for day. Excel returns 582 in cell D4 meaning there are 582 days between "11/20/2010" and "6/24/2012".

There is an easier way to calculate days between two dates in Excel, simply subtract the dates.

Formula in cell D4:

For example,

the first date is 1/1/2030 and the second date is 1/2/2030, both calculations return 1 day, however, you may think it is two days between the dates. This is because if you calculate the end date inclusive or not.

If you want to calculate the end date also included then use this:

Formula in cell D4:

The picture above shows you this issue in cell E4.

A much easier formula is to simply subtract the earlier date from the later date. Excel dates are actually numbers between 1 and 99999 formatted as dates, this allows you to do mathematical operations to dates.

You can see that yourself by selecting a cell containing a date and then press CTRL + 1. This opens a dialog box where you can see how the cell is formatted.

Press with mouse on General to show the number. 1/1/2017 is in fact 42736. Number 1 is 1/1/1900.

If you use dates and time and want to calculate the number of days and hours between two dates use the following formula:

The result is displayed in cell F5 on the picture above.

INT(C5-B5)& " days "

The INT function removes the decimal part from the number returning complete days. The & (ampersand) concatenates the number with the text string " days".

HOUR(B5-C5-INT(B5-C5))&" hours"

The HOUR  function returns a number representing the hour. The decimal part of the number is the time, in this case, hours. To get the decimal part simply subtract the integer part from the number, this is where the INT function comes in.

Lastly, the ampersand & character concatenates the hour number with " hours". You can get even greater detail by using the MINUTE and SECOND functions as well.

Get Excel *.xlsx

Date difference in days.xlsx

5. Calculate days between two dates ignoring the years

This example demonstrates how to calculate days between two given dates ignoring the years using the DATEDIF function. The first date is specified in cell B3 and the second date in cell C3.

Formula in cell D5:

The third argument determines the unit to calculate, in this case, it is "YD" which stands for days ignoring years. Excel returns 276 in cell D5 meaning there are 276 days between November 20th and June 24th.

6. Calculate complete months between two dates

This example displays how to calculate complete months between two given dates using the DATEDIF function. The first date is specified in cell B6 and the second date in cell C6.

Formula in cell D6:

The third argument determines the unit to calculate, in this case, it is "M" which stands for complete months. Excel returns 14 in cell D6 meaning there are 14 complete months between "8/15/2013" and "10/27/2014".

7. DATEDIF function not working

Don't use the "MD" argument, it may return incorrect results.

Make sure Excel recognizes the date as an Excel date. If the date is interpreted as a text value no calculations can be done.

3. How to use the DATEVALUE function

What is the DATEVALUE function?

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text.

1. Introduction

What are dates in Excel?

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

Why are dates stored as text in Excel?

Dates are not stored as text in Excel they are stored as numbers, however, Excel tries to identify values as text, numbers, Boolean values, dates, and time automatically but may fail in rare occasions.

This may happen when you import data from the internet, databases, text files, and other sources. You can convert the dates to Excel dates if you like but it can be a tedious and time consuming task.

The DATEVALUE function lets you convert the text date in the formula to an Excel date without you first converting them on the worksheet.

How to enter valid dates in Excel?

Valid dates are right-aligned in a cell just like numbers, invalid dates are identified as text values and left-aligned. The image above shows that / (forward slash) and - (dash) are valid delimiting characters in Excel dates, demonstrated in cells B2 and B3.

A dot and no delimiters at all, shown in cells B4 and B5, are invalid, these dates are left-aligned by Excel. The DATEVALUE cant properly identify these date values either, you need to change these dates by hand or using the methods described below.

2. DATEVALUE function Syntax

DATEVALUE(date_text)

3. DATEVALUE function Arguments

date_text

Required. The date stored as text you want to convert to an Excel date (serial number).

4. DATEVALUE function example

A date stored as text can't be sorted, filtered or be used in Excel date calculations. You need to convert the date stored as text to a date that Excel recognizes. The image above shows dates stored as text in cell range B3:B7.

Formula in cell C3:

Explaining formula

DATEVALUE(B3)

becomes

DATEVALUE("1/4/2013")

and returns

41278 which is the corresponding serial number Excel uses for the date "1/4/2013".

5. DATEVALUE function not working

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must be a valid text date which is determined by your date and time settings (national settings) on your operating system.

Formula in cell C3:

5.1. Verify your date and time settings

Microsoft recommends that you verify your date and time settings so they are compatible with the text date format. On a "Windows 10" operating system open the Control Panel and then "National Settings" and see if you can find a supported date setting. Both the short and long dates must match.

Back to top

5.2. Convert a text date to an Excel date using a formula

I could not find a setting that worked with the text date above so I created the following formula in cell C3.

The picture above shows the output of the formula in column C.

Back to top

5.2.3. Explaining the formula in cell C3

Step 1 - Extract year from string

To extract the year from the text date in cell B3 I simply use the RIGHT function to extract the 4 last characters.

The RIGHT function extracts a specific number of characters always starting from the right.

RIGHT(text,[num_chars])

RIGHT(B3, 4)

becomes

RIGHT("Jan 21 1996", 4)

and returns 1996.

Step 2 - Extract month abbreviation from string

The month number is a little harder since it is built of three letters. To get the three letters from B3 I use the LEFT function.

The LEFT function extracts a specific number of characters always starting from the left.

LEFT(text, [num_chars])

LEFT(B3, 3)

becomes

LEFT("Jan 21 1996", 3)

and returns "Jan".

Step 3 - Convert month to a number

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(LEFT(B3, 3), {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)

becomes

MATCH("Jan", {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)

and returns 1. "Jan" is the first value in the array.

Step 4 - Extract day from string

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(text, start_num, num_chars)

MID(B3, 4, 3)

becomes

MID("Jan 21 1996", 4, 3)

and returns " 21".

Step 5 - Create an Excel date

The DATE function allows you to create an Excel date based on a year, month, and day number.
The arguments are DATE(year, month, day).

DATE(RIGHT(B3, 4), MATCH(LEFT(B3, 3), {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), MID(B3, 4, 3))

becomes

DATE(1996, 1, 21)

and returns 1/21/1996 in cell C3.

Back to top

Get Excel *.xlsx file

DATEVALUE function not working.xlsx

5.3 Example

This example shows that the DATEVALUE function can't handle a date with a dot as a delimiting character.

Formula in cell D3:

The DATEVALUE function returns a #VALUE! error.

How to create an Excel date from dates using dots as delimiting characters?

The SUBSTITUTE function lets you replace a given text string with a new text string.

Formula in cell D3 that works:

Explaining formula

Step 1 - Substitute dots with forward slash

The SUBSTITUTE function replaces a specific text string in a value. Case sensitive.

Function syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(B3,".","/")

becomes

SUBSTITUTE("1.4.2013",".","/")

and returns

"1/4/2013".

Step 2 - Convert text date to an Excel date

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text.

Function syntax: DATEVALUE(date_text)

DATEVALUE(SUBSTITUTE(B3,".","/"))

becomes

DATEVALUE("1/4/2013")

and returns 41278. Here is how to format the cell value as a date:

1. Select the cell (D3).
2. Press CTRL + 1 to open the "Format Cells.." dialog box.
   
3. Select category : Date
4. Pick a date formatting.
5. Press with mouse on the "OK" button.

This example shows that the DATEVALUE function can't handle a date without delimiting characters.

Formula in cell D3:

The DATEVALUE function returns a #VALUE! error.

How to create an Excel date from dates without delimiting characters?

Formula in cell D3 that works:

Explaining formula

Step 1 - Extract year from date

The RIGHT function extracts a specific number of characters always starting from the right.

Function syntax: RIGHT(text,[num_chars])

RIGHT(B3,4)

becomes

RIGHT("01042013",4)

and returns "2013".

Step 2 - Extract month from date

The LEFT function extracts a specific number of characters always starting from the left.

Function syntax: LEFT(text, [num_chars])

LEFT(B3,2)

becomes

LEFT("01042013",2)

and returns "01".

Step 3 - Extract day from date

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(B3,3,2)

becomes

MID("01042013",3,2)

and returns "04".

Step 4 - Convert to an Excel date

The DATE function returns a number that acts as a date in the Excel environment.

Function syntax: DATE(year, month, day)

DATE(RIGHT(B3,4),LEFT(B3,2),MID(B3,3,2))

becomes

DATE("2013","01","04")

and returns 41278.

4. How to use the DAY function

What is the DAY function?

The DAY function extracts the day as a number from an Excel date.

1. DAY Function Syntax

DAY(serial_number)

Back to top

2. DAY Function Arguments

serial_number

Required. The Excel date value you want to extract the day number from.

Excel dates are actually numbers formatted as dates. 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.

Back to top

3. DAY function example

The image above demonstrates a formula in cell C3 that extracts the day number from a date, however, the date must be an Excel date.

Formula in cell C3:

To understand the DAY function I need to explain that it needs an Excel date in order to calculate the day properly.  The date in cell B3 is an Excel date meaning it is a number formatted as a date. 1 is 1/1/1900 and 1/1/2000 is 36526 meaning there are 36526 days between the dates.

You can verify this, select a cell containing 1/1/2000 and press CTRL + 1 to open the "Format Cells" dialog box.

Back to top

4. DAY function not working

• Check your spelling.
• Make sure the date is an Excel date.

How do I know the date is recognized as an Excel date?
Check the "Format Cells" dialog box and press with left mouse button on category "General". You should see a number and not a date.

How do I convert a date to an Excel date?
Try using the DATEVALUE function to convert a text date to an Excel date. Read this article if it is not working.

Back to top

5.1 DAY function alternative - TEXT function

Formula in cell C3:

5.1.1 Explaining formula

Step 1 - TEXT function

The TEXT function lets you format values.

TEXT(value, format_text)

value - The string you want to format. You can use a cell reference here or use a text string.

format_text - Formatting code allowing you to change the way, for example, a date or a number is displayed to the Excel user.

Step 2 - Populate arguments

The TEXT function has two arguments.

value - B3

format_text - "d"

"d" is an abbreviation for day. A single "d" returns the day like this: 1. A double "dd" returns 01.

Check out this article to learn more about formatting codes in the TEXT function.

Step 3 - Evaluate TEXT function

TEXT(B3, "d")

becomes

TEXT(43263, "d")

and returns 12.

Back to top

5.2 DAY function alternative - Cell formatting

You can also show the day number using cell formatting, the image above shows cell formatting applied to cell C5.

How to apply cell formatting:

1. Select cell C3.
2. Press CTRL + 1 to open the "Format Cells" dialog box.
3. Press with left mouse button on "Category" Custom, see the image above.
4. Enter d below Type:
5. Press with left mouse button on the OK button to apply changes.

Back to top

6. Filter dates based on a given day number

Formula in cell D3:

Explaining formula

Step 1 - Calculate day number for each date value

DAY(B3:B11)

becomes

DAY({44667; 44667; 44700; 44707; 45006; 45051; 45054; 45075; 45079})

and returns

{16; 15; 16; 26; 16; 16; 8; 29; 16}.

Step 2 - Compare month number to condition

The equal sign lets you check if values are equal, note that this does not perform a case-sensitive comparison. Check out the EXACT function if upper and lower letters matter.

DAY(B3:B11)=16

becomes

{16; 15; 16; 26; 16; 16; 8; 29; 16}=16

and returns

{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}.

Step 3 - Extract dates meeting the condition

The FILTER function is a new function available to Excel 365 subscribers. It lets you extract values based on a condition or criteria.

FILTER(array, include, [if_empty])

FILTER(B3:B11, DAY(B3:B11)=16)

becomes

FILTER({44667; 44667; 44700; 44707; 45006; 45051; 45054; 45075; 45079}, {TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE})

and returns

{44667; 44700; 45006; 45051; 45079}.

Back to top

7. Create a dynamic calendar using the DAY function

The formula in cell B4 is a dynamic array formula and works only in Excel 365. The SEQUENCE function is only available to Excel 365 subscribers.

The formula uses the provided date in cell B2 to calculate all dates for that month. Make sure you only use Excel dates in cell B2 and that the date is the first date in any given month.

The dates in B4:H9 automatically refresh as soon as a new date is entered in cell B2, this is why it is called a dynamic calendar.

Excel 365 formula in cell B4:

Explaining formula

The dynamic array formula in cell B4 is entered as a regular formula, however, it spills values to the right and below as far as needed.

Make sure the adjacent cells are empty or a #SPILL! error is shown.

Step 1 - Calculate weekday

This step is needed in order to calculate the first date in the week, it is most often not the same date as the first date in a month. It is the 27th in this example, shown in the image above.

The WEEKDAY function returns a number representing the weekday in a week. 1 - Sunday, 2 - Monday, and so on.

WEEKDAY(serial_number,[return_type])

WEEKDAY(B2,1)

becomes

WEEKDAY(44621, 1)

and returns 3. This means that March the 1st, 2022 is a Tuesday.

Step 2 - Create an array large enough to populate a monthly calendar

The SEQUENCE function creates a list of sequential numbers to a cell range or array. It is located in the Math and trigonometry category and is only available to Excel 365 subscribers.

SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(6,7)

returns

{1, 2, 3, 4, 5, 6, 7; 8, 9, 10, 11, 12, 13, 14; 15, 16, 17, 18, 19, 20, 21; 22, 23, 24, 25, 26, 27, 28; 29, 30, 31, 32, 33, 34, 35; 36, 37, 38, 39, 40, 41, 42}.

Note the delimiting characters used in the array above, the comma separates values between columns and the semicolon between rows. Your setup may use other delimiting characters, you can change these in the regional settings.

Step 3 - Calculate the date for the first sunday

This is often a date in the previous month. The plus and minus sign lets you perform arithmetic calculations in an Excel formula.

B2-WEEKDAY(B2,1)+SEQUENCE(6,7)

becomes

44621 - 3 + {1, 2, 3, 4, 5, 6, 7; 8, 9, 10, 11, 12, 13, 14; 15, 16, 17, 18, 19, 20, 21; 22, 23, 24, 25, 26, 27, 28; 29, 30, 31, 32, 33, 34, 35; 36, 37, 38, 39, 40, 41, 42}

and returns

{44619, 44620, 44621, 44622, 44623, 44624, 44625; 44626, 44627, 44628, 44629, 44630, 44631, 44632; 44633, 44634, 44635, 44636, 44637, 44638, 44639; 44640, 44641, 44642, 44643, 44644, 44645, 44646; 44647, 44648, 44649, 44650, 44651, 44652, 44653; 44654, 44655, 44656, 44657, 44658, 44659, 44660}

Step 4 - Calculate the day for each date in the array

DAY(B2-WEEKDAY(B2,1)+SEQUENCE(6,7))

becomes

DAY({44619, 44620, 44621, 44622, 44623, 44624, 44625; 44626, 44627, 44628, 44629, 44630, 44631, 44632; 44633, 44634, 44635, 44636, 44637, 44638, 44639; 44640, 44641, 44642, 44643, 44644, 44645, 44646; 44647, 44648, 44649, 44650, 44651, 44652, 44653; 44654, 44655, 44656, 44657, 44658, 44659, 44660})

and returns

{27, 28, 1, 2, 3, 4, 5; 6, 7, 8, 9, 10, 11, 12; 13, 14, 15, 16, 17, 18, 19; 20, 21, 22, 23, 24, 25, 26; 27, 28, 29, 30, 31, 1, 2; 3, 4, 5, 6, 7, 8, 9}.

Back to top

5. How to use the DAYS function

What is the DAYS function?

The DAYS function calculates an integer that represents the number of days between two dates.

There is, actually, an easier way to calculate the number of days between two dates. Simply subtract C3 by B3 like this: = C3-B3 and you will get the number of days.

The DATEDIF function is also able to calculate the number of days between two dates.

What are dates in Excel?

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

Related functions

Excel Function	Description
DAY(date)	Returns the day of the month (1-31)
DAYS(end_date, start_date)	Returns the number of days between two dates
NETWORKDAYS(start_date, end_date)	Returns the number of workdays between two dates
DATEDIF(start_date, end_date, unit)	Returns the time between two dates in specified units like years, monts, etc
EDATE(start_date, months)	Returns the date that is the indicated number of months before or after start_date
EOMONTH(start_date, months)	Returns the last day of the month before or after start_date by months
NETWORKDAYS.INTL(start_date, end_date, weekend)	Returns workdays between dates with custom weekend parameters
WORKDAY(start_date, days)	Returns a date days away from the start_date, skipping weekends

DAYS function Syntax

DAYS(end_date, start_date)

DAYS function Arguments

end_date	Required. The end date of the date range you want to calculate.
start_date	Required. The start date of the date range you want to calculate.

DAYS function example

This example shows the DAYS function in cell D3, in the image above, calculating the difference in days between the specified cells C3 and B3.

Formula in cell D3:

The DAYS function calculates 1 day between 1/1/2030 and 1/2/2030. Why, you may wonder? There are two days in that date range? It depends how you calculate.  1/1/2030 is 47484 and 1/2/2030 is 47485. 47485 - 47484 equals 1.

This is because if you calculate the end date inclusive or not. If you want to include the end date in the calculation then use this:

Formula in cell D3:

DAYS function tips and tricks

Why are dates sometimes stored as text in Excel?

Excel tries to identify values as text, numbers, Boolean values, dates, and time automatically but may fail in rare occasions. This may happen when you import data from the internet, databases, text files, and other sources. You can convert the dates to Excel dates if you like but it can be a tedious and time consuming task.

There is no need to convert text dates to Excel dates, the DAYS function handles text dates properly.

DAYS function not working

The image above shows two dates containing a dot as a delimiting character, this makes Excel return an error.

The DAYS function returns #VALUE! error if a date is not recognized, here is a formula to fix this problem: DATEVALUE function

6. How to use the EDATE function

What is the EDATE function?

The EDATE function returns a date determined by a start date and a number representing the months. Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

What are dates in Excel?

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

What is a maturity date?

The maturity date is the date on which the principal amount of a security becomes due and payable to the holder. It applies to fixed-income securities like bonds, notes, bills where the issuer must repay the principal on the maturity date.

At maturity, the debt is fully repaid. The security might cease to exist after maturity. Maturity dates affect interest rate risk - longer terms have higher risk. The final coupon payment is made on the maturity date.

What is a due date?

The due date is the date on which a debt payment is due to be paid by a borrower to a lender. It applies to loans, mortgages, credit cards, accounts receivables where periodic payments are required.

A due date recurrence can be monthly, quarterly, annually based on the terms. Missing a due date can result in late fees or interest charges.

Related functions

Excel Function	Description
DATEDIF(start_date, end_date, unit)	Returns the time between two dates in specified units like complete years or months
EDATE(start_date, months)	Returns the date that is the indicated number of months before or after start_date
EOMONTH(start_date, months)	Returns the last day of the month before or after start_date by months
MONTH(date)	Returns the month of a date (1-12)
DATE(year, month, day)	Returns an Excel date

EDATE function Syntax

EDATE(start_date, months)

EDATE function Arguments

start_date	Required. A start date.
months	Required. The number of months before or after start_date. You are allowed to use negative numbers here. A negative number makes the function return an earlier date than the start date.

The first argument is an Excel date, make sure it is not invalid. The second argument is an integer, negative values are also possible.

EDATE function example

The image above demonstrates the EDATE function in cells D3 and D4, the start dates are specified in cells B3 and B4. The number of months are in cells C3 and C4.

Formula in cell D3:

The first date in cell B3 is "1/15/2017", the number of months in cell C3 and the result in cell D3 is "9/15/2017" which is exactly 8 months later.

Calculate quarterly dates

This example demonstrates how to calculate four different dates in the same calculation using arrays in Excel. The calculated dates are exactly three months a part from the start date.

Dynamic array formula in cell D3:

{3;6;9;12} is an array of numbers separated by a semicolon. Excel performs four different calculations in the same cell and returns an array to cell D3 and the cells below as far as needed. This is called spilling and is a new feature in Excel 365.

Calculate bi-monthly dates

This example demonstrates how to calculate six different dates in the same calculation using arrays in Excel. The calculated dates are exactly two months a part from the start date.

Dynamic array formula in cell D3:

Explaining formula in cell D3

Step 1 - Create a sequence from 2 to 12 with step 2

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(6,,2,2)

returns

{2;4;6;8;10;12}

Step 2 - Calculate dates

EDATE(B3,SEQUENCE(6,,2,2))

becomes

EDATE(B3,{2;4;6;8;10;12})

and returns

{42809;42870;42931;42993;43054;43115}.

EDATE function tips and tricks

Use a negative number as the month argument to get a date before the start date. The image above shows the start date in cell B3, -2 in cell C3 which represents the month argument and the result in cell D3.

2/27/2030 minus two months is 12/27/2029

EDATE function not working

An invalid date demonstrated in cell B5 returns a #VALUE! error displayed in cell D5. Use the method explain here to convert invalid dates to dates that work: DATEVALUE function

The EDATE function truncates a decimal value representing months argument, shown in cell C3 and D3.

What is truncate?

Removing the decimal leaving only the whole number. For example, truncate 7.9 and you get 7.

EDATE function alternative

The DATE function creates an Excel date based on three arguments: year, month, and day. It can be used to calculate a date for a given number of months in the future, or in the past, just like the EDATE function.

• year - a number containing four digits equal to or larger than 1900.
• month -  a number between 1 and 12 representing the months in a year: January to December.
• a - variable representing the months argument in the EDATE function.
• day - a number representing the day of the date from 1 to 31.

7. How to use the EOMONTH function

What is the EOMONTH function?

The EOMONTH function returns an Excel date for the last day of a given month using a number and a start date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

What is EOMONTH an abbreviation of?

EOMONTH stand for End Of month.

What are dates in Excel?

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

What is a maturity date?

The maturity date is the date on which the principal amount of a security becomes due and payable to the holder. It applies to fixed-income securities like bonds, notes, bills where the issuer must repay the principal on the maturity date.

At maturity, the debt is fully repaid. The security might cease to exist after maturity. Maturity dates affect interest rate risk - longer terms have higher risk. The final coupon payment is made on the maturity date.

What is a due date?

The due date is the date on which a debt payment is due to be paid by a borrower to a lender. It applies to loans, mortgages, credit cards, accounts receivables where periodic payments are required.

A due date recurrence can be monthly, quarterly, annually based on the terms. Missing a due date can result in late fees or interest charges.

Related functions

Excel Function	Description
DATEDIF(start_date, end_date, unit)	Returns the time between two dates in specified units like complete years or months
EDATE(start_date, months)	Returns the date that is the indicated number of months before or after start_date
EOMONTH(start_date, months)	Returns the last day of the month before or after start_date by months
MONTH(date)	Returns the month of a date (1-12)
DATE(year, month, day)	Returns an Excel date

EOMONTH function Syntax

EOMONTH(start_date, months)

EOMONTH function Arguments

start_date	Required. A date that represents the starting date. Don't use text dates, use Excel dates, see comments below.
months	Required. A number that determines how many months before or after the start date. A negative value returns a date earlier than the start_date.

EOMONTH function example

The image above shows the EOMONTH function cell D3 calculating the last day of the month based on a start date specified in cell B3 and the number of months specified in cell C3.

Formula in cell D3:

The function calculates 5/31/2030 based on the start date 1/1/2030 and add 4 months to that. The last day in May 2030 is 31.

Calculate quarterly dates for the last day of a given month

This example demonstrates how to calculate four different dates in the same calculation using arrays in Excel. The calculated dates are exactly three months a part from the start date and is the last date in that particular month.

Formula in cell D3:

{3;6;9;12} is an array of numbers separated by a semicolon. Excel performs four different calculations in the same cell and returns an array to cell D3 and the cells below as far as needed. This is called spilling and is a new feature in Excel 365.

Calculate bi monthly dates for the last day of a given month

This example demonstrates how to calculate six different dates in the same calculation using arrays in Excel. The calculated dates are exactly two months a part from the start date and is the last date in that particular month.

Formula in cell D3:

Explaining formula in cell D3

Step 1 - Create a sequence from 2 to 12 with step 2

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(6,,2,2)

returns

{2;4;6;8;10;12}

Step 2 - Calculate dates

EOMONTH(B3,SEQUENCE(6,,2,2))

becomes

EOMONTH(B3,{2;4;6;8;10;12})

and returns

{49399;49460;49521;49582;49643;49705}.

EOMONTH function tips

Use a negative number as the month argument to get a date before the start date. The image above shows the start date in cell B3, -2 in cell C3 which represents the month argument and the result in cell D3.

1/1/2035 minus two months is November 2034 and the last date in that month is 11/30/2034.

EOMONTH function not working

An invalid date demonstrated in cell B5 returns a #VALUE! error displayed in cell D5. Use the method explain here to convert invalid dates to dates that work: DATEVALUE function

The EOMONTH function truncates a decimal value representing the months argument.

What is truncate?

Removing the decimal leaving only the whole number. For example, truncate 7.9 and you get 7.

EOMONTH function alternative

Formula in cell D3:

Explaining formula in cell D3

Step 1 - Calculate the year

The YEAR function converts a date to a number representing the year in the date.

Function syntax: YEAR(serial_number)

YEAR(B3)

becomes

YEAR(49310)

and returns "2035".

Step 2 - Calculate the month

The MONTH function extracts the month as a number from an Excel date.

Function syntax: MONTH(serial_number)

MONTH(B3) +C3 + 1

becomes

MONTH(49310) + 4 + 1

becomes

1 + 4 + 1

and returns 6.

Step 3 - Calculate the Excel date

The DATE function returns a number that acts as a date in the Excel environment.

Function syntax: DATE(year, month, day)

DATE(YEAR(B3),MONTH(B3)+C3+1,1)

becomes

DATE(2035,6,1)

and returns "6/1/2035" (49460)

Step 4 - Subtract Excel date by 1

DATE(YEAR(B3),MONTH(B3)+C3+1,1) - 1

becomes

49460 - 1

and returns

49459 (5/31/2035)

8. How to use the HOUR function

What is the HOUR function?

The HOUR function returns an integer representing the hour of an Excel time value. The returning number is ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

1. What is an integer?

An integer is a whole number that can be positive, negative, or zero, but not a fraction or decimal. Excel can't calculate the hour based on negative integers.

What is an Excel time value?

Excel time is actually a decimal number ranging between 0  and 1 in Excel and then formatted as time.

For example, 12:00 PM is represented as 0.5 because it is half of a day, you can verify this by typing 12:00 PM in a cell and then change the cell formatting to general. This will show the value as Excel interprets it.

How does Excel recognize time values?

Excel recognizes certain text strings like "6:45 PM" as valid time values. A recognized time value is right aligned in the cell just like a regular number, shown in the image below in cell B2.

A time number that is not recognized is left aligned which is demonstrated in cell B4 in the image above. This visual feedback lets you easily spot values that need closer inspection.

What is an hour in an Excel time value?

1/24 or approx. 0.04166667 represents one hour in Excel time value. Excel uses a number 0 <= x <=  1 in decimal form to represent time in a worksheet. 0 is zero hours and 1 is 24 hours. To get one hour we must divide 1 by 24 and we get 1/24.

Why is 1 equal to 24 hours?

This has to do how Excel handles dates. Each date is represented by an integer and one day is 1 in Excel.

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

What is the decimal form?

The decimal form or decimal number system represents values using digits 0-9 and a decimal point. Each digit position represents a power of 10 - units, tens, hundreds, etc. The decimal point separates the integer part from the fractional part. Decimal values allow representation of fractional numbers in a standard notation.

Where is AM/PM used?

AM/PM is used in the United States, Canada, Mexico, Australia, United Kingdom and a few other countries. AM refers to times from midnight to noon. PM refers to times from noon to midnight.

Where is the 24 hour clock used?

The 24 hour clock is used in continental Europe including Scandinavia, China, India and most countries except those above. Used in formal documents like schedules, timetables, business contracts. Used by scientists, aviation, hospitals, transportation services, military. Avoids AM/PM ambiguity and potential confusion.

AM/PM	24 hour clock
12:00 AM	0:00
1:00 AM	1:00
2:00 AM	2:00
3:00 AM	3:00
4:00 AM	4:00
5:00 AM	5:00
6:00 AM	6:00
7:00 AM	7:00
8:00 AM	8:00
9:00 AM	9:00
10:00 AM	10:00
11:00 AM	11:00
12:00 PM	12:00
1:00 PM	13:00
2:00 PM	14:00
3:00 PM	15:00
4:00 PM	16:00
5:00 PM	17:00
6:00 PM	18:00
7:00 PM	19:00
8:00 PM	20:00
9:00 PM	21:00
10:00 PM	22:00
11:00 PM	23:00
12:00 AM	0:00

2. HOUR function Syntax

HOUR(serial_number)

3. HOUR function Arguments

serial_number

Required. An Excel time value that you want to calculate the hour of.

4. HOUR function example

This example demonstrates the HOUR function in cell C3, it extracts the hour from the Excel time value specified in cell B3: "1:34:22 PM"

Formula in cell C3:

1:00 PM represents the 13th hour.

5. HOUR function not working

The HOUR function returns #NUM! error if the decimal is a negative value.

The HOUR function returns #VALUE! error if the Excel time value is invalid.

6. HOUR function alternative - cell formatting

You also have the option to format a cell containing an Excel time value so it shows only the hour part. Here is how:

1. Select the cell.
2. Press CTRL + 1 to open the "Format Cells" dialog box.
3. Select category: Custom
4. Create a new type: hh
5. Press the "OK" button.

The image above shows cell B3 containing the following time value "1:34:22 PM", cell C3 references that value. This means that cell C3 contains the same value, however, cell formatting is applied which makes it show only the hour part.

7. Count complete hours between two time values

The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5.

Formula in cell D5:

Note that this formula works only with time entries, not date and time entries. It doesn't matter if the start time is past the end time, the formula will regardless return a positive hour value.

Explaining formula in cell D5

Step 1 - Subtract time entries

C5-B5

becomes

6:00:00 AM - 12:00:00 AM

Time entries in excel are actually a decimal value between 0 (zero) and 1. One hour is 1/24, six hours is 6/24 and so on.

0.25 - 0 equals 0.25

Step 2 - Remove sign

The ABS function removes the minus sign if it exists, the HOUR function can't calculate negative values.

ABS(C5-B5)

becomes

ABS(0.25) and returns 0.25.

Step 3 - Convert decimal to hour(s)

The HOUR function calculates hours based on a decimal value.

HOUR(ABS(C5-B5))

becomes

HOUR(0.25)

and returns 6.

The image above demonstrates a formula the returns negative hours if the start time is later than the end time.

Formula in cell D5:

Explaining formula in cell D5

This part of the formula HOUR(ABS(C5-B5)) is explained above.

Step 1 - Calculate if end date is smaller than the start date

C5<B5

becomes

0.25<0

and returns FALSE.

Step 2 - Return -1 if TRUE and 1 if FALSE

IF(C5<B5,-1,1)

becomes

IF(0.25<0,-1,1)

becomes

IF(FALSE,-1,1) and returns 1.

Step 3 - Multiply with result

HOUR(ABS(C5-B5))*IF(C5<B5,-1,1)

becomes

6*IF(C5<B5,-1,1)

becomes

6*1 and returns 6 in cell D5.

8. Count complete hours between two date and time values

The picture above shows a formula that calculates complete hours between date and time entries.

Formula in cell D5:

If you want negative hours then remove the ABS function:

The TEXT function formats the result of C5-B5 into complete hours.

9. How to use the MINUTE function

What is the MINUTE function?

The MINUTE function returns a whole number that represents the minute based on an Excel time value. The returned number is an integer between 0 (zero) and 59.

What is an integer?

An integer is a whole number that can be positive, negative, or zero, but not a fraction or decimal. Excel can't calculate the minute based on negative integers.

What is an Excel time value?

Excel time is actually a decimal number ranging between 0  and 1 in Excel and then formatted as time.

For example, 12:00 PM is represented as 0.5 because it is half of a day, you can verify this by typing 12:00 PM in a cell and then change the cell formatting to general. This will show the value as Excel interprets it.

How does Excel recognize time values?

Excel recognizes certain text strings like "6:45 PM" as valid time values. A recognized time value is right aligned in the cell just like a regular number, shown in the image below in cell B2.

A time number that is not recognized is left aligned which is demonstrated in cell B4 in the image above. This visual feedback lets you easily spot values that need closer inspection.

What is a minute in an Excel time value?

There are 60 minutes in one hour and 24 hours in one day. 60 * 24 = 1440 minutes in one day.

1/1440 or approx. 0.000694 represents one minute in Excel time value. Excel uses a number 0 <= x <=  1 in decimal form to represent time in an Excel worksheet. 0 is zero minutes and 1 is 1440 minutes (24 hours).

Why is 1 day equal to 1440 minutes?

This has to do how Excel handles dates. Each date is represented by an integer and one day is equal to 1 in Excel. There are 24 hours or 1440 minutes in one day.

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

Related functions

Excel Function	Description
HOUR(time)	Returns the hour portion of a time value
MINUTE(time)	Returns the minute portion of a time value
SECOND(time)	Returns the second portion of a time value
TIME(hour,minute,second)	Creates a time value from hour, minute, and second values
TIMEVALUE(time_text)	Converts a text time to a serial time value

MINUTE function Syntax

MINUTE(serial_number)

MINUTE function Arguments

serial_number

Required. An Excel time value that you want to extract the minute from.

MINUTE function example

This example demonstrates the MINUTE function in cell C3, it extracts the minute from the Excel time value specified in cell B3: "1:34:22 PM"

Formula in cell C3:

The MINUTE function returns 34.

MINUTE function not working

The MINUTE function returns #NUM! error if the decimal is a negative value.

The MINUTE function returns #VALUE! error if the Excel time value is invalid.

10. How to use the MONTH function

What is the MONTH function?

The MONTH function extracts the month as a number from an Excel date.

• 1 - January
• 2 - February
• 3 - March
• 4 - April
• 5 - May
• 6 - June
• 7 - July
• 8 - August
• 9 - September
• 10 - October
• 11 - November
• 12 - December

1. MONTH Function Syntax

MONTH(serial_number)

Back to top

2. MONTH Function Arguments

serial_number

Required. The date value you want to extract the month from.

Back to top

3. MONTH Function not working

Check your spelling, the image above shows the MONTH function misspelled. An #NAME? error is shown in cell C3.

Cell B3 contains a text value and not a proper Excel date, this returns a #VALUE! error in cell C3.

Back to top

4. MONTH Function example

Formula in cell C3:

To understand the MONTH function I need to explain that it needs an Excel date in order to calculate the month properly.  The date in cell B3 is an Excel date meaning it is a number formatted as a date. 1 is 1/1/1900 and 1/1/2000 is 36526 meaning there are 36526 days between the dates.

You can verify this, select a cell containing 1/1/2000 and press CTRL + 1 to open the "Format Cells" dialog box.

The "Sample" shown in the image above demonstrates what the value changes to if you choose to format the cells using the category "General". The sample contains 36526 which is the number representing 1/1/2000.

Back to top

5. MONTH Function alternative

Formula in cell C3:

Explaining formula

Step 1 - TEXT function

The TEXT function lets you format values.

TEXT(value, format_text)

value - The string you want to format. You can use a cell reference here or use a text string.

format_text - Formatting code allowing you to change the way, for example, a date or a number is displayed to the Excel user.

Step 2 - Populate arguments

The TEXT function has two arguments.

value - B3

format_text - "m"

"m" is an abbreviation for month. A single "m" returns the number representing the position of a given month in a year.

Check out this article to learn more about formatting codes in the TEXT function.

Step 3 - Evaluate TEXT function

TEXT(B3, "m")

becomes

TEXT(45089, "m")

and returns 6. 6 represents the sixth month in a year which is "June".

Back to top

6. MONTH function - return month name instead of a number

6.1 Example 1 - INDEX function

Formula in cell C3:

Explaining formula in cell C3

Step 1 - Calculate month number

MONTH(B3)

• 1 - January
• 2 - February
• 3 - March
• 4 - April
• 5 - May
• 6 - June
• 7 - July
• 8 - August
• 9 - September
• 10 - October
• 11 - November
• 12 - December

MONTH(B3)

becomes

MONTH(45089)

and returns 6. Number 6 represents "June".

Step 2 - Return corresponding month name

The INDEX function returns a value in a cell range or array based on a row and column number (optional).

INDEX(array, [row_num], [column_num], [area_num])

INDEX({"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},MONTH(B3))

becomes

INDEX({"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, 6)

and returns "June". "June" is the sixth month in a year.

Back to top

6.2 Example 2 - TEXT function

Formula in cell C4:

Explaining formula in cell C4

The TEXT function lets you format values.

TEXT(value, format_text)

value - The string you want to format. You can use a cell reference here or use a text string.

format_text - Formatting code allowing you to change the way, for example, a date or a number is displayed to the Excel user.

Formatting code "mmmm" evaluates to the month name.

TEXT(B4, "mmmm")

becomes

TEXT(44603, "mmmm")

and returns "March".

Back to top

6.3 Example 3 - Cell formatting

You can also show the month name using cell formatting, the image above demonstrates cell formatting applied to cell C5.

How to apply cell formatting:

1. Select cell C5.
2. Press CTRL + 1 to open the "Format Cells" dialog box.
3. Press with left mouse button on "Category" Custom, see the image above.
4. Enter mmmm below Type:
5. Press with left mouse button on OK button to apply cahnges.

Back to top

7. Filter dates based on month

Formula in cell D3:

Explaining formula

Step 1 - Calculate month number for each date value

MONTH(B3:B11)

becomes

MONTH({44667; 44667; 44700; 44707; 45006; 45051; 45054; 45075; 45079})

and returns {4; 4; 5; 5; 3; 5; 5; 5; 6}.

Step 2 - Compare month number to condition

The equal sign lets you check if values are equal, note that this does not perform a case-sensitive comparison. Check out the EXACT function if upper and lower letters matter.

MONTH(B3:B11)=5

becomes

{4; 4; 5; 5; 3; 5; 5; 5; 6}=5

and returns

{FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE}

Step 3 - Extract dates meeting the condition

The FILTER function is a new function available to Excel 365 subscribers. It lets you extract values based on a condition or criteria.

FILTER(array, include, [if_empty])

FILTER(B3:B11, MONTH(B3:B11)=5)

becomes

FILTER({44667; 44667; 44700; 44707; 45006; 45051; 45054; 45075; 45079}, {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE})

and returns {44700; 44707; 45051; 45054; 45075}.

Back to top

More than 1300 Excel formulas

Excel categories

Leave a Comment