Date and Time Functions – A to M
Table of Contents
- How to use the DATE function
- How to use the DATEDIF function
- How to use the DATEVALUE function
- How to use the DAY function
- How to use the DAYS function
- How to use the EDATE function
- How to use the EOMONTH function
- How to use the HOUR function
- How to use the MINUTE function
- How to use the MONTH function
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.
Table of Contents
- Introduction
- Excel function syntax
- Function arguments
- DATE Function Example
- Add or subtract months and days
- Date function example
- What does the Date function do?
- Date function format
- Date function not working
- How to make Excel order by date
- Date function add days
- Date function subtract days
- Date function add months
- Last day of a given month
- Get Excel file
- Calculate date given weekday and week number (week starts on Sunday)
- Calculate date given weekday and week number (week starts on Monday)
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. |
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.
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:
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.
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:
- Select the cell.
- Press CTRL + 1 to open the "Format Cells" dialog box.
- Select Date in the Category:, see the image above.
- Pick a formatting type.
- 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:
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.
9. Date function not working
- Did you spell the DATE function correctly?
Excel returns a #NAME? error if you didn't. - 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 - Did you use exactly three arguments?
Excel returns a dialog box: You've entered too many arguments for this function.
DATE(year, month, day) - 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 - 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.
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.
- Press with right mouse button on any date in column B.
A popup menu appears.
- Press "Sort". Another popup menu appears.
- 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:
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.
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:
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.
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:
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.
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:
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).
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:
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).
15. Get Excel file
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.
La Thăng asks:
I want to find the day if given date and week, for example: if given Tuesday, 32nd week, 2015 >>> how to create a formula to point out 4/8/2015?
Formula in cell D6:
Explaining formula in cell D6
Step 1 - Calculate the first date of this year
The TODAY function returns today's date, the function is volatile meaning it recalculates every time you enter a value in a cell and press enter or press F9.
TODAY() returns 5/8/21.
Step 2 - Calculate the first date of this year
The YEAR function returns the year based on an Excel date.
YEAR(TODAY())
becomes
YEAR(44324)
and returns 2021.
Step 3Â - Calculate the first date of this year
The DATE function returns an Excel date based on a year number, month number and day number.
DATE(year, month, day)
Year is a number from 1900 to 9999, month is a number from 1 to 12. 1 = January, ... , 12 = December. Day is usually a number between 1 and 31 but can be larger.
DATE(YEAR(TODAY()), 1, 1)
becomes
DATE(2021, 1, 1)
and returns 44197.
Step 4 - Subtract with the corresponding weekday number
The WEEKDAY function converts an Excel date to a number ranging from 1 to 7, based on when the week begins.
WEEKDAY(serial_number, [return_type])
If you live in the US the week in starts on Sundays, return_type is 1.
WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 1)
becomes WEEKDAY(44197, 1)
and returns 6. 44197 is 1/1/2021 and that day is a Friday.
1 - Sunday, ... , 7 - Saturday.
Step 5 - Subtract date with weekday number
DATE(YEAR(TODAY()), 1, 1)-WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 1)
becomes
44197 - 6 equals 44191.
Step 6 - Calculate and add the number of days from Monday/week 1 to Monday/(nth week -1)
+7*(D4-1)
Example, 7*(32-1)
becomes
7*31 and returns 217.
Step 7 - Calculate and add weekday number
The MATCH function returns the relative position of the given item in a cell range or array.
MATCH($D$4, {"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}, 0)
If you week begins on sunday, this is the array you want to be using:
{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}.
Example, "Tue" returns 3. "Tue" is in position three in the array.
Step 8 - Add and subtract
44192+217+2 = 44411
44411 is 8/3/2021.
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.
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.
Table of Contents
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
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.
Table of Contents
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.
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.
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.
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:
- Select the cell (D3).
- Press CTRL + 1 to open the "Format Cells.." dialog box.
- Select category : Date
- Pick a date formatting.
- 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.
Table of Contents
1. DAY Function Syntax
DAY(serial_number)
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.
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.
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.
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.
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:
- Select cell C3.
- Press CTRL + 1 to open the "Format Cells" dialog box.
- Press with left mouse button on "Category" Custom, see the image above.
- Enter d below Type:
- Press with left mouse button on the OK button to apply changes.
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}.
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}.
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.).
Table of Contents
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:
- Select the cell.
- Press CTRL + 1 to open the "Format Cells" dialog box.
- Select category: Custom
- Create a new type: hh
- 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
Table of Contents
1. MONTH Function Syntax
MONTH(serial_number)
2. MONTH Function Arguments
serial_number | Required. The date value you want to extract the month from. |
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.
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.
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".
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.
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".
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:
- Select cell C5.
- Press CTRL + 1 to open the "Format Cells" dialog box.
- Press with left mouse button on "Category" Custom, see the image above.
- Enter mmmm below Type:
- Press with left mouse button on OK button to apply cahnges.
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}.
More than 1300 Excel formulasExcel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form