Date and Time Functions – N to Z
Table of Contents
- How to use the NETWORKDAYS function
- How to use the NETWORKDAYS.INTL function
- How to use the NOW function
- How to use the SECOND function
- How to use the TIME function
- How to use the TIMEVALUE function
- How to use the TODAY function
- How to use the WEEKDAY function
- How to use the WEEKNUM function
- How to use the WORKDAY function
- How to use the YEAR function
- How to use the YEARFRAC function
1. How to use the NETWORKDAYS function
The NETWORKDAYS function returns the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify.
Formula in cell D3:
What is the difference between the NETWORKDAYS.INTL function and the NETWORKDAYS function?
The NETWORKDAYS.INTL function lets you use custom weekend parameters. Actually they don't have to be a week end day, it can be a weekday also. This makes it really versatile for all sorts of calculations like how many mondays are there between a given start and end date.
Related functions
Excel Function | Description |
---|---|
NETWORKDAYS.INTL(start_date, end_date, weekend) | Returns the number of workdays between two dates, excluding custom weekend parameters |
NETWORKDAYS(start_date, end_date) | Returns the number of workdays between two dates, excluding weekends |
WORKDAY(start_date, days) | Returns a date adjusted by a number of workdays, excluding weekends |
DATEDIF(start_date, end_date, unit) | Calculates the time between two dates in specified units like years, months, days |
DAYS(end_date, start_date) | Returns the number of days between two specified dates |
Table of Contents
1. NETWORKDAYS Function Syntax
NETWORKDAYS(start_date, end_date, [holidays])
2. NETWORKDAYS Function Arguments
start_date | Required. The start date you want to use in the function. |
end_date | Required. The end date you want to use. |
[holidays] | Optional. Excludes this list of dates from being counted. |
3. NETWORKDAYS Function example
The formula in cell K6 counts weekdays (Monday to Friday) between two dates.
Formula in cell K6:
3.1 Explaining formula
Step 1 - NETWORKDAYS function
The NETWORKDAYS function returns the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify.
NETWORKDAYS(start_date, end_date, [holidays])
Step 2 - Populate arguments
The NETWORKDAYS function has three arguments, the third is optional.
start_date -Â K3
end_date -Â K4
[holidays] - Not used here
Step 3 - Evaluate NETWORKDAYS function
NETWORKDAYS(K3, K4)
becomes
NETWORKDAYS(44631, 44648)
and returns 12.
4. Create a list of weekdays only
This example demonstrates how to extract a list of weekdays (Monday to Friday) or networking days. The formula in cell J7 uses the two dates specified in cells K3 and K4 to create a list of weekdays.
Excel 365 formula:
Explaining formula
This formula is a dynamic array formula and works only in Excel 365, it is entered as a regular formula
Step 1 - Calculate days between dates and add one
The minus and plus signs let you perform arithmetic operations in an Excel formula.
K4-K3+1
becomes
44648-44631+1
equals 18.
Step 2 - Create a sequence of numbers from 0 (zero) to 18
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(K4-K3+1,,0)
becomes
SEQUENCE(18,,0)
and returns
{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}.
Step 3 - Add start date to sequence of numbers
SEQUENCE(K4-K3+1,,0)+K3
becomes
{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17} + 44631
and returns
{44631; 44632; 44633; 44634; 44635; 44636; 44637; 44638; 44639; 44640; 44641; 44642; 44643; 44644; 44645; 44646; 44647; 44648}.
Step 4 - Calculate WEEKDAY number
WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)
becomes
WEEKDAY({44631; 44632; 44633; 44634; 44635; 44636; 44637; 44638; 44639; 44640; 44641; 44642; 44643; 44644; 44645; 44646; 44647; 44648},1)
and returns
{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}.
Step 5 - Check if weekday number is smaller than 7
Seven is the last weekday in a week and it represents Saturday if the second argument is one. The smaller than character lets you compare values, the result is a boolean value TRUE or FALSE.
WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7
becomes
{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}<7
and returns
{TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE}.
Step 6 - Check if weekday numbers are larger than one
One represents Sunday and we want to identify dates thare equal to Monday to Friday, in other words, networkingdays.
WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1
becomes
{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}>1
and returns
{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}.
Step 7 - Multiply arrays
Both values on the same position in the arrays must return TRUE meaning we need to perform AND-logic. The asterisk lets us multiply values in an Excel formula.
TRUE * TRUE = 1
TRUE * FALSE = 0 (zero)
FALSE * FALSE = 0 (zero)
Boolean values have numerical equivalents, TRUE is equal to 1 and FALSE is equal to 0 (zero).
(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1)
becomes
{TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE} * {TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}
and returns
{1; 0; 0; 1; 1; 1; 1; 1; 0; 0; 1; 1; 1; 1; 1; 0; 0; 1}.
Step 8 - Filter dates based on conditions
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(SEQUENCE(K4-K3+1,,0)+K3,(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1))
becomes
FILTER({44631; 44632; 44633; 44634; 44635; 44636; 44637; 44638; 44639; 44640; 44641; 44642; 44643; 44644; 44645; 44646; 44647; 44648},{1; 0; 0; 1; 1; 1; 1; 1; 0; 0; 1; 1; 1; 1; 1; 0; 0; 1})
and returns
{44631; 44634; 44635; 44636; 44637; 44638; 44641; 44642; 44643; 44644; 44645; 44648}.
Step 9 - Shorten formula
The LET function allows you to name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
x - SEQUENCE(K4-K3+1, , 0)+K3
y - WEEKDAY(x, 1)
LET(x, SEQUENCE(K4-K3+1, , 0)+K3, y, WEEKDAY(x, 1), COUNT(FILTER(x, (y<7)*(y>1))))
5. Count weekdays in a month
Formula in cell K6:
Explaining formula
Steps 1 to 3 calculate the first date of the specified month in cell K3. Steps 4 to 6 calculate the last date in the specified month.
Step 1 - Calculate year based on date
The YEAR function returns a number representing the year from a given date.
YEAR(date)
YEAR(K3)
becomes
YEAR(44631)
and returns 2022.
Step 2 - Calculate month based on date
The MONTH function returns a number representing the month from a given date.
MONTH(date)
MONTH(K3)
becomes
MONTH(44631)
and returns 3.
Step 3 - Calculate first date based on year and month
The DATE function returns an Excel date based on a year, month, and day number.
DATE(year, month, day)
DATE(YEAR(K3), MONTH(K3), 1)
becomes
DATE(2022, 3, 1)
and returns 44621. (3/1/2022)
Step 4 - Calculate year based on the date
YEAR(K3)
becomes
YEAR(44631)
and returns 2022.
Step 5 - Calculate year based on month
MONTH(K3)+1
becomes
MONTH(44631)+1
and returns 4.
Step 6 - Calculate the last date based on year and month
DATE(YEAR(K3),MONTH(K3)+1,1)-1
becomes
DATE(2022, 4, 1)-1
becomes
44652-1
and returns 44651. (3/31/2022)
Step 7 - Calculate the number of days between two dates
NETWORKDAYS(DATE(YEAR(K3),MONTH(K3),1),DATE(YEAR(K3),MONTH(K3)+1,1)-1)
becomes
NETWORKDAYS(44621, 44651)
and returns 23.
6. NETWORKDAYS Function - holidays
Excel contains a great function that easily counts the number of workdays between two dates, it even allows you to specify holidays that are not counted.
Formula in cell C4:
The image above shows two dates in cell range B5:B6Â that acts as holidays in this example.
Formula in cell C8:
NETWORKDAYS.INTL function is available for Excel 2010 and later versions, it allows you to use custom weekends meaning if you want only Sundays to be weekend you can do that.
The string "1101111" means that all days except Wednesdays are weekends (isn't that great?) in the formula above, see row 9 and 10 above.
2. How to use the NETWORKDAYS.INTL function
What is the NETWORKDAYS.INTL function?
The NETWORKDAYS.INTL function calculate the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify. You may specify which days are weekend days.
What is the difference between the NETWORKDAYS.INTL function and the NETWORKDAYS function?
The NETWORKDAYS.INTL function lets you use custom weekend parameters. Actually they don't have to be a week end day, it can be a weekday also. This makes it really versatile for all sorts of calculations like how many mondays are there between a given start and end date.
Related functions
Excel Function | Description |
---|---|
NETWORKDAYS.INTL(start_date, end_date, weekend) | Returns the number of workdays between two dates, excluding custom weekend parameters |
NETWORKDAYS(start_date, end_date) | Returns the number of workdays between two dates, excluding weekends |
WORKDAY(start_date, days) | Returns a date adjusted by a number of workdays, excluding weekends |
DATEDIF(start_date, end_date, unit) | Calculates the time between two dates in specified units like years, months, days |
DAYS(end_date, start_date) | Returns the number of days between two specified dates |
1. NETWORKDAYS.INTL Syntax
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
2. NETWORKDAYS.INTL Arguments
start_date | Required. |
end_date | Required. |
[weekend] | Optional. Allows you to specify which days are weekend days using a number o a string. |
[holidays] | Optional. Excludes date(s) from being counted. |
Weekend numbers
Number | Weekend days |
1 (default value) | Saturday, Sunday |
2 | Sunday, Monday |
3 | Monday, Tuesday |
4 | Tuesday, Wednesday |
5 | Wednesday, Thursday |
6 | Thursday, Friday |
7 | Friday, Saturday |
11 | Sunday only |
12 | Monday only |
13 | Tuesday only |
14 | Wednesday only |
15 | Thursday only |
16 | Friday only |
17 | Saturday only |
You may also specify weekend days using a string containing only 1 and 0 (zero).
- 1 - weekend
- 0 - workday
Example, 1110011 considers only Thursdays and Fridays as workdays, all other days in the week are weekend days.
3. NETWORKDAYS.INTL example
Formula in cell D3:
4. NETWORKDAYS.INTL example - count a specific weekday between two dates
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate in this article counts, for example, Mondays or any weekday in a date range. Later in this article, I will show you how to exclude holidays.
Update! I recommend using the NETWORKDAYS.INTL function to count weekdays, it is a lot smaller and easier to work with.
Regular formula in cell D2:
Copy cell D2 and paste to cell range E2:J2. You need to change "1000000" to "0100000" in cell E2, in cell F2 change it to "0010000" and so on.
The text string "1000000" allows you to specify which days are weekdays and which are weekends. There are seven characters and they can be 1 or 0 (zero), 1 indicates it is to be counted and 0 not to be counted.
However, in this case, I am using the string to exclude specific days, I am not using it to define which days are weekends.
4.1 Exclude holidays
Update! You can use the NETWORKDAYS.INTL function to count weekdays and ignore holidays as well.
Formula in cell D7:
Array formula in cell D7 (old formula):
How to create an array formula
- Select cell D7.
- Paste array formula.
- Press and hold Ctrl + Shift.
- Press Enter.
How to copy array formula
- Copy cell D7.
- Select cell range E7:J7.
- Paste.
5. Count a specific weekday between two dates - alternative formula
Array formula in cell D2 (old formula):
How to create an array formula
- Select cell D2.
- Paste array formula.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter.
- Release all keys.
How to copy array formula
- Copy cell D2.
- Select cell range E2:J2.
- Paste.
Explaining formula in cell D2
Step 1 - Create dates in the date range
$B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1)
becomes
41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-(41000-1)))-1)
becomes
41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)
becomes
41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)
becomes
41000+(ROW($A$1:INDEX($A$1:$A$1000, 30))-1)
becomes
41000+(ROW($A$1:$A$30)-1)
becomes
41000+({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}-1)
becomes
41000+{0, 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}
and returns
{41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}
Step 2 - Convert dates to days of the week
TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")
becomes
TEXT({41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}, "ddd")
and returns
{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}
Step 3 - Check if values in array are equal to the value in cell D1 (Mon)
IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0)
becomes
IF({"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}, "ddd")="Mon", 1, 0)
and returns
{0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1}
Step 4 - Sum values in array
SUMPRODUCT(IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0))
becomes
SUMPRODUCT({0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1})
and returns 5 in cell D2.
3. How to use the NOW function
What is the NOW function?
The NOW function returns the current date and time. It is a volatile function.
What is time in Excel?
Excel time value is a number equal to or larger than 0 (zero) and smaller than 1, formatted as a time value. One hour is 1/24, there are 24 hours in one day.
One minute is 1/1440, there are 1440 minutes in one day (60*24 = 1440). One second is 1/86400, there are 86400 seconds in one day (60*60*24 = 86400).
The following table shows whole hours, one hour is 1/24, 2 hours is 2/24, and so on.
0 - 12:00:00 AM
1/24 - 1:00:00 AM
2/24 - 2:00:00 AM
...
23/24 - 11:00:00 PM
24/24 - 12:00:00 AM
The time value is only the decimal part of a number, in other words, a value larger than or equal to 1 makes no difference, Excel uses only the decimal part of a number to create an Excel time value.
1.5 -> 0.5 -> 12:00:00 PM
The whole numbers represent dates in Excel. The whole number and the decimal part create a date and time value. Here is an example: 1.5 represents 1/1/1900 12:00 PM
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.
What is a volatile function?
The NOW function is a volatile function, it updates each time you recalculate the worksheet.
What is the effect of volatile functions?
They may slow down your worksheet/workbook if you have many volatile functions. Use with caution, it may slow down your workbook considerably if used extensively.
When is the worksheet calculated?
Cells containing non volatile functions are only calculated once or until you force a recalculation, however, volatile functions are recalculated each time you type in a cell and press enter.
Can you stop recalculating a worksheet?
Yes, you can change a setting to manual recalculations.
- Go to tab "Formulas".
- Press with left mouse button on the "Calculation Options" button, a popup menu appears.
- Press with mouse on "Manual".
This stops the automatic recalculations.
How to force a recalculation?
Pressing F9 key will recalculate or refresh all the formulas and values in every worksheet of every workbook you have open.
Pressing Shift+F9 will only recalculate the formulas and values on the single worksheet you're currently viewing or active.
Pressing Ctrl+Alt+F9 is the quickest way to force a full recalculation of absolutely everything in all open workbooks, even if nothing has changed. It ignores whether changes were made or not and completely recomputes.
Are there more volatile functions in Excel?
Yes. OFFSET, TODAY, RAND among others.
Function | Syntax | Description |
---|---|---|
OFFSET | OFFSET(reference, rows, cols) | Returns a cell offset from a reference cell. |
TODAY | TODAY() | Returns the current date. |
RAND | RAND() | Returns a random decimal between 0 and 1. |
RANDARRAY | RANDARRAY([rows], [columns], [min], [max], [whole_number]) | Returns an array with random numbers. |
RANDBETWEEN | RANDBETWEEN(bottom, top) | Returns a random whole number between bottom and top |
Note, that conditional formatting is extremely volatile or super-volatile meaning it is recalculated as you scroll through a worksheet.
NOW function example
Formula in cell B3:
NOW function Syntax
NOW()
NOW function Arguments
The NOW function has no arguments.
NOW function not working
4. How to use the SECOND function
What is the SECOND function?
The SECOND function returns an integer representing the second based on an Excel time value. The returning number ranges from 0 to 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 second based on a negative Excel time value.
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 second 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. There are 60 seconds in one minute, an entire day contains 60 * 1440 = 86,400 seconds.
1/86400 or approx. 0.0000115740740740741 represents one second 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 86400 seconds (24 hours).
Why is 1 equal to 86,400 seconds?
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, or 86,400 seconds 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.
SECOND function Syntax
SECOND(serial_number)
SECOND function Arguments
serial_number | Required. An Excel time value that you want to extract the second from. |
SECOND function Example
Formula in cell C3:
Comments
Excel time (serial_number)Â is actually a number ranging between 0 and 1 in Excel and then formatted as time.
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.
5. How to use the TIME function
What is the TIME function?
The TIME function returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M.
Formula in cell D3:
Table of Contents
1. TIME Function Syntax
TIME(hour, minute, second)
2. TIME Function Arguments
hour | Required. A number between 0 and 32767 represents the hour. |
minute | Required. A number between 0 and 32767 represents the minute. |
second | Required. A number between 0 and 32767 represents the second. |
3. What is time in Excel?
Excel time value is a number equal to or larger than 0 (zero) and smaller than 1, formatted as a time value. One hour is 1/24, there are 24 hours in one day.
One minute is 1/1440, there are 1440 minutes in one day (60*24 = 1440). One second is 1/86400, there are 86400 seconds in one day (60*60*24 = 86400).
The following table shows whole hours, one hour is 1/24, 2 hours is 2/24, and so on.
0 - 12:00:00 AM
1/24 - 1:00:00 AM
2/24 - 2:00:00 AM
...
23/24 - 11:00:00 PM
24/24 - 12:00:00 AM
The time value is only the decimal part of a number, in other words, a value larger than or equal to 1 makes no difference, Excel uses only the decimal part of a number to create an Excel time value.
1.5 -> 0.5 -> 12:00:00 PM
The whole numbers represent dates in Excel. The whole number and the decimal part create a date and time value. Here is an example: 1.5 represents 1/1/1900 12:00 PM
4. TIME Function example
The TIME function creates an Excel time value meaning a number equal to or larger than 0 (zero) and smaller than 1 formatted as a time value.
Formula in cell E3:
Explaining formula
Step 1 - TIME function
TIME(hour, minute, second)
Step 2 - Populate arguments
TIME(hour, minute, second)
hour - B3
minute - C3
second - D3
Step 3 - Evaluate formula
TIME(1, 30, 20)
and returns 0.062731481 (1:30:20 AM).
1 hour = 1/24
30 min = 30/1440
20 sec = 20/86400
1/24 + 30/1440 + 20/86400 = 0.062731481
4.1 Time function - Hour value larger than 24
An hour value greater than 23 will be divided by 24 and the remaining hours will be returned by the function.
27/24 = 1.125 The decimal part is 0.125 and is equal to 3 hours. 3/24 = 0.125
4.2 Time function - Minute value larger than 59
A minute value equal to or greater than 60 will be divided by 60, the whole number is hours and the remaining minutes will be minutes.
119/60 is approx. 1.983333 The TIME function returns 1:59:00 AM. 0.983333 is approx. 59 minutes.
4.3 Time function - Seconds value larger than 59
A "second" value equal to or greater than 60 will be divided by 60 and added to minutes, the remaining seconds are returned.
7200/86400 is approx. 0.083333 which is the same as 120 minutes or 2 hours.
5. TIME Function - how to add hours
Formula in cell F3:
Alternative formula:
Note, the TIME function arguments are limited to 32767. Larger values return #NUM errors.
Explaining formula
Step 1 - Calculate hours in decimals
D3/24
becomes
5/24 equals 0.208333333.
Step 2 - Add time
B3+D3/24
becomes
0.979166667 + 5/24
becomes
0.979166667 + 0.208333333 equals 1.1875
6. TIME Function - how to add minutes
Formula in cell F3:
Alternative formula:
Note, the TIME function arguments are limited to 32767. Larger values return #NUM errors.
Explaining formula
Step 1 - Calculate hours in decimals
The division slash charcater lets you divide numbers in an Excel formula.
D3/24
becomes
5/1440 is approx. 0.0034722
Step 2 - Add time
The plus sign lets you add numbers in an Excel formula.
B3+D3/1440
becomes
0.979166667 + 5/1440
becomes
0.979166667 + 0.0034722
and is approx. 0.982638889
7. TIME Function - how to add seconds
Formula in cell F3:
Alternative formula:
Note, the TIME function arguments are limited to 32767. Larger values return #NUM errors.
Explaining formula
Step 1 - Calculate seconds in decimals
D3/24
becomes
5/86400
and is approx. 0.0000578
Step 2 - Add time
B3+D3/24
becomes
0.979166667 + 5/86400
becomes
0.979166667 + 0.0000578
and is approx. 0.979224537037037
8. How to calculate more than 24 hours?
The image above shows how to display an Excel time value larger than 1. This is possible using a different cell formatting code than the default one Excel uses.
- Select cell F3.
- Press CTRL + 1 to open the "Format Cells" dialog box.
- Select the "Custom" category.
- Use the following formatting code:
[h]:mm:ss - Press with left mouse button on OK button.
Explaining formula in cell F3
Step 1 - Calculate Excel time value
Cell F3 contains an Excel time value larger than 1.
B3+B5/24
becomes
0.979166666666667 + B5/24
becomes
0.979166666666667 + 60/24
becomes
0.979166666666667 + 2.5
and returns 3.47916666666667 in cell F3. Cell F3 is formatted using the following cell formatting code: [h]:mm:ss which shows 83:30:00 in cell F3.
Step 2 - Verify calculation
We can easily verify the calculation.
3*24 = 72 hours
0.47916666666667 * 24 equals 11.5 hours.
72 + 11.5 = 83.5 hours -> 83:30:00
9. Convert time to 24 hour clock
The TEXT functionlets you convert AM/PM to a 24 hour clock.
Formula in cell D3:
Explaining formula
Step 1 - TEXT function
The TEXT function converts a value to text in a specific number format.
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 allows you to change the way, for example, a date or a number is displayed to the Excel user. |
Step 2 - Populate TEXT function arguments
TEXT(value, format_text)
value - B3
format_text - "hh:mm:ss"
hh - hours using two digits
mm - minutes (two digits)
ss - seconds (two digits)
Step 3 - Evaluate TEXT function
TEXT(B3,"hh:mm:ss")
becomes
TEXT(0.0627314814814815, "hh:mm:ss")
and returns 01:30:20.
1/24 + 30/1440 + 20/86400 equals 0.0627314814814815.
6. How to use the TIMEVALUE function
What is the TIMEVALUE function?
The TIMEVALUE function returns a decimal number representing an Excel time value, based on a text string. Excel uses decimal numbers between 0 and 0.99988426 formatted as time. 0 (zero) is 12:00:00 AM and 0.99988426 is 11:59:59 P.M.
The TIMEVALUE function in Excel is rarely needed to convert values in formulas. Excel automatically handles conversion of numbers, text, logical values, and error values as required. TIMEVALUE function is provided for compatibility with other spreadsheet applications. In most cases, Excel will format values correctly without requiring the TIMEVALUE function function.
Only use TIMEVALUE function if you find Excel is not properly converting a value to text in a specific formula. In general, you can avoid using it entirely since Excel auto-converts types seamlessly in most situations.
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.
Does the TIMEVALUE function ignore dates?
Yes, the TIMEVALUE function ignores the date part , example "22-Aug-2019 6:35 PM" returns 0.774305556 which is equal to "6:35 PM".
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.
How to show the decimal value as an Excel time value?
- Select the cell containing the decimal value.
- Press CTRL + 1 to open the "Format Cells" dialog box shown in the image above.
- Select category "Time".
- Select a type.
- Press with left mouse button on the "OK" button.
Why convert time values to decimal numbers?
The conversion allows you to perform arithmetic operations to time values, this works only if the time value is represented as a numerical value.
This lets you add or subtract, for instance, hours, minutes, and seconds easily using simple Excel formulas. It also lets you perform average, median, and other calculations using specific Excel functions.
TIMEVALUE function Syntax
TIMEVALUE(time_text)
TIMEVALUE function Arguments
time_text | Required. A text string within quotation marks that represent time, for example "03:23 PM" or "22:21". |
TIMEVALUE function example
The image above demonstrates the TIMEVALUE function in cells B3 to B8, it converts time values as text values to decimal numbers.
Formula in cell B3:
The decimal numbers shown in cells B3 to B8 are the actual Excel time representation of:
- 1:48 AM
- 6:11 PM
- 9:58 AM
- 11:59:59 PM
- 0:00 AM
- 12:00 PM
7. How to use the TODAY function
What is the TODAY function?
The TODAY function returns the Excel date (serial number) of the current date.
What is a volatile function?
The TODAY function is a volatile function, it updates or recalculates every time the worksheet is recalculated. This may slow down your workbook calculations considerably if there are many formulas that depend on the TODAY function.
It may also slow down your worksheet/workbook if you have many volatile functions.
When is the worksheet calculated?
Cells containing non volatile functions are only calculated once or until you force a recalculation, however, volatile functions are recalculated each time you type in a cell and press enter.
Can you stop recalculating a worksheet?
Yes, you can change a setting to manual recalculations.
- Go to tab "Formulas".
- Press with left mouse button on the "Calculation Options" button, a popup menu appears.
- Press with mouse on "Manual".
This stops the automatic recalculations.
How to force a recalculation?
Pressing F9 key will recalculate or refresh all the formulas and values in every worksheet of every workbook you have open.
Pressing Shift+F9 will only recalculate the formulas and values on the single worksheet you're currently viewing or active.
Pressing Ctrl+Alt+F9 is the quickest way to force a full recalculation of absolutely everything in all open workbooks, even if nothing has changed. It ignores whether changes were made or not and completely recomputes.
Are there more volatile functions in Excel?
Yes. OFFSET, TODAY, RAND, NOW among others.
Function | Syntax | Description |
---|---|---|
OFFSET | OFFSET(reference, rows, cols) | Returns a cell offset from a reference cell. |
NOW | NOW() | Returns the current time. |
RAND | RAND() | Returns a random decimal between 0 and 1. |
RANDARRAY | RANDARRAY([rows], [columns], [min], [max], [whole_number]) | Returns an array with random numbers. |
RANDBETWEEN | RANDBETWEEN(bottom, top) | Returns a random whole number between bottom and top |
Note, that conditional formatting is extremely volatile or super-volatile meaning it is recalculated as you scroll through a worksheet.
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.
TODAY Function Syntax
TODAY()
TODAY Function Arguments
The TODAY function has no arguments
TODAY Function example
Formula in cell B4:
What does the TODAY function return?
The Excel date the TODAY function returns is a serial number that Excel recognizes and can be filtered, sorted and used in other date calculations.
Excel dates are actually serial numbers formatted as dates, 1/1/1900 is 1 and 2/2/2018 is 43133. There are 43132 days between 2/2/2018 and 1/1/1900.
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.
Filter the upcoming dates for next week
This example demonstrates a formula that extracts records from cell range B3:C18 if the dates fall during the next seven days. The formula automatically changes the date
Excel 365 formula in cell E5:
This formula works only in Excel 365, the FILTER function may return multiple values that automatically spills to cells below.
Explaining formula
An Excel date is a serial number that Excel recognizes and can be filtered, sorted and used in other date calculations.
Excel dates are actually serial numbers formatted as dates, 1/1/1900 is 1 and 2/2/2018 is 43133. There are 43132 days between 2/2/2018 and 1/1/1900.
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.
Step 1 - Add 7 days to today's date
The plus sign + lets you perform addition in an Excel formula.
TODAY()+7
becomes
45241+7
equals 45248 (11/18)
Step 2 - Check dates smaller than today plus 7 days
The less than sign. larger than sign, and the equal sign are comparison operators that let you create logical expressions. The logical expression returns the boolean value, TRUE or FALSE.
B3:B18<=TODAY()+7
becomes
{45249; 45244; 45234; 45245; 45245; 45251; 45241; 45241; 45252; 45247; 45241; 45251; 45252; 45253; 45240; 45240}<=45248
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}
Step 3 - Check dates later than or equal to today
B3:B18>=TODAY()
{45249; 45244; 45234; 45245; 45245; 45251; 45241; 45241; 45252; 45247; 45241; 45251; 45252; 45253; 45240; 45240}<=45241
and returns
{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE}
Step 4 - Multiply boolean values to perform AND logic
The asterisk character lets you multiply numbers and boolean values in an Excel formula. The numerical equivalent to boolean value TRUE is 1 and FALSE is 0 (zero)
(B3:B18<=TODAY()+7)*(B3:B18>=TODAY())
becomes
{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE} * {TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE}
and returns
{1;1;0;1;1;0;0;0;0;1;0;0;0;0;0;0}
Step 5 - Filter records
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(B3:C18,(B3:B18<=TODAY()+7)*(B3:B18>=TODAY()))
becomes
FILTER(B3:C18,{1;1;0;1;1;0;0;0;0;1;0;0;0;0;0;0})
and returns the following array in cell E5 and spills to adjacent cells to the right and below as far as needed.
{45249, "#001"; 45244, "#007"; 45245, "#016"; 45245, "#012"; 45247, "#009"}
8. How to use the WEEKDAY function
What is the WEEKDAY function?
The WEEKDAY function converts a date to a number from 1 to 7 corresponding to a weekday or weekend day. You can customize the function so the week starts with any weekday.
Table of Contents
1. Introduction
What is a weekday?
A weekday refers to the days of the week that are typically considered work days or business days in parts of the world that follow a Monday to Friday work week. Saturday and Sunday are usually considered weekend days, not weekdays.
What is a weekend day?
A weekend day is Saturday and Sunday in most calendars. A weekend day refers to the days of the week that are typically considered non-work days in parts of the world that follow a standard Monday to Friday work week.
2. WEEKDAY Function Syntax
WEEKDAY(serial_number,[return_type])
3. WEEKDAY Function Arguments
serial_number | Required. The Excel date value you want to extract the WEEKDAY number from. |
return_type | Optional. Determines the type of return value, see table below. |
Why is the date a serial number?
The Excel date system uses a serial number system to represent dates, where each day is represented by a unique number. This system makes it easier to perform calculations and comparisons with dates. The serial number for January 1, 1900 is 1, and each subsequent day is represented by a serial number that increases by 1.
Return_type | Number returned |
1 or omitted | Numbers 1 to 7 for Sunday to Saturday. |
2 | Numbers 1 to 7 for Monday to Sunday. |
3 | Numbers 0 to 6 for Monday to Sunday. |
11 | Numbers 1 to 7 for Monday to Sunday. |
12 | Numbers 1 to 7 for Tuesday to Monday. |
13 | Numbers 1 to 7 for Wednesday to Tuesday. |
14 | Numbers 1 to 7 for Thursday to Wednesday. |
15 | Numbers 1 to 7 for Friday to Thursday . |
16 | Numbers 1 to 7 for Saturday to Friday . |
17 | Numbers 1 to 7 for Sunday to Saturday. |
4. Video
5. WEEKDAY function example
The WEEKDAY function shown in cell D3 in the image above calculates a number based on a date. The number represents a given weekday based on the provided value in the second argument.
The formula below has no second argument, it defaults to 1 meaning the week begins with a Sunday.
Formula in cell D3:
6. WEEKDAY function not working
- Check your spelling.
- Check that you are using a valid Excel date.
- Check the number of arguments.
7. WEEKDAY function - show name
Formula in cell C3:
=TEXT(B3, "DDDD")
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 - "dddd"
"dddd" returns the weekday. Lower and upper case letters make no difference.
Check out this article to learn more about formatting codes in the TEXT function.
Step 3 - Evaluate TEXT function
TEXT(B3, "dddd")
becomes
TEXT(43263, "dddd")
and returns "Tuesday".
Formula in cell C4:
=INDEX({"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}, WEEKDAY(B4,1))
Explaining formula
Step 1 - Calculate number representing a weekday
WEEKDAY(B4,1)
becomes
WEEKDAY(43184, 1)
and returns 1.
Step 2 - Return weekday name based on position in array
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({"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}, WEEKDAY(B4,1))
becomes
INDEX({"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}, 1)
and returns "Sunday". "Sunday" is the first value in the array.
8. Count days between two given weekdays and two dates
The image above shows an Excel 365 formula that counts days between two dates and also if they are between two given weekdays.
The start date is specified in cell K3, end date is in cell K4. The start weekday is 3 which represents Tuesday, the table in J11:K18 shows the numbers and corresponding weekday names. The end weekday is 5 which represents Thursday.
The calendar in cell range B4:H9 shows which days (bolded) meet the criteria. Cell K9 displays the result, there are six bolded days in the calendar.
Excel 365 dynamic array formula in cell K6:
=LET(x,SEQUENCE(K4-K3+1,,0),y,WEEKDAY(x+K3,1),COUNT(FILTER(x,(y<=K7)*(y>=K6))))
Explaining formula
COUNT(FILTER(SEQUENCE(K4-K3+1,,0),(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<=K7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>=K6)))
Step 1 - Calculate the number of days and add one
The minus and plus signs let you perform arithmetic operations in an Excel formula
K4-K3+1
44648 - 44631 +1
equals 18.
Step 2 - Create a sequence of numbers from 0 to 18
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(K4-K3+1,,0)
becomes
SEQUENCE(18,,0)
and returns
{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}.
Step 3 - Add date to sequence of numbers
The plus sign lets you perform add numbers in an Excel formula.
SEQUENCE(K4-K3+1,,0)+K3
becomes
{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17} + 44631
and returns
{44631; 44632; 44633; 44634; 44635; 44636; 44637; 44638; 44639; 44640; 44641; 44642; 44643; 44644; 44645; 44646; 44647; 44648}.
Step 4 - Calculate weekday
WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)
becomes
WEEKDAY({44631; 44632; 44633; 44634; 44635; 44636; 44637; 44638; 44639; 44640; 44641; 44642; 44643; 44644; 44645; 44646; 44647; 44648},1)
and returns
{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}.
Step 5 - Check if number is less than seven
The less than and larger than characters let you check if a value is smaller or larger than a condition, the result is a boolean value TRUE or FALSE. The equal sign and the less than character combined compare if values are equal or smaller than a condition.
WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<=K7
becomes
{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}<=5
and returns
{FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE}.
Step 6 - Check if weekday is larger than one
WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>=K6
becomes
{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}>=3
and returns
{TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE}.
Step 7 - Multiply arrays
The asterisk character lets you multiple values in an Excel formula. Multiplying boolean values always return their numerical equivalents.
TRUE -> 1
FALSE -> 0 (zero)
(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1)
becomes
{FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE}*{TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE}
and returns
{0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0}.
Step 8 - Filter numbers based on 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(SEQUENCE(K4-K3+1,,0),(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1))
becomes
FILTER({0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17},{0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0})
and returns
{4; 5; 6; 11; 12; 13}.
Step 9 - Count numbers in the array
The COUNT function counts all numbers in a cell range or array.
COUNT(value1, [value2], ...)
COUNT(FILTER(SEQUENCE(K4-K3+1,,0),(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1)))
becomes
COUNT({4; 5; 6; 11; 12; 13})
and returns 6. There are six numbers in the array.
Step 10 - Shorten formula
The LET function allows you to name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
LET(x, SEQUENCE(K4-K3+1, , 0), y, WEEKDAY(x+K3, 1), COUNT(FILTER(x, (y<7)*(y>1))))
x - SEQUENCE(K4-K3+1,,0)
y - WEEKDAY(x+K3,1)
9. How to calculate a date based on specific weekday in a month
Question:
How to calculate the date of the third Monday of a given month?
Answer:
Column B contains dates of the first date of a month, however, they are formatted as Month and year. This makes it possible to use the corresponding cell value in our formula.
Excel 365 dynamic array formula in cell C3:
Array formula in cell C3
To enter an array formula, type the formula in cell C3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.
Don't enter the curly brackets yourself, they appear automatically.
Explaining array formula in cell C3
Step 1 - Create an array of numbers corresponding to days in month
The EOMONTH function calculates the last date in a given month, the DAY function then returns the day of that date.
ROW(INDIRECT( "$1:$"&DAY( EOMONTH(B3, 0))))-1)
becomes
ROW(INDIRECT( "$1:$"&DAY( EOMONTH(39814, 0))))-1)
becomes
ROW(INDIRECT( "$1:$"&DAY( 39844)))-1)
becomes
ROW(INDIRECT( "$1:$"&DAY( 39844)))-1)
becomes
ROW(INDIRECT("$1:$"&31))-1
The INDIRECT function converts a text string to a cell reference that an Excel function then can use.
ROW(INDIRECT($1:$31))-1
becomes
ROW($1:$31)-1
The ROW function then returns the row numbers of each cell in the cell reference.
ROW($1:$31)-1
becomes
{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
and returns {0; 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}
Step 2 - Check if date is a Monday
The array in the step before is now going to be added to the first date in the given month.
WEEKDAY(B3 +ROW(INDIRECT( "$1:$"&DAY( EOMONTH(B3, 0))))-1)=2
becomes
WEEKDAY(B3 +{0; 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})=2
becomes
WEEKDAY({39814; 39815; 39816; 39817; 39818; 39819; 39820; 39821; 39822; 39823; 39824; 39825; 39826; 39827; 39828; 39829; 39830; 39831; 39832; 39833; 39834; 39835; 39836; 39837; 39838; 39839; 39840; 39841; 39842; 39843; 39844})=2
It is worth mentioning that Excel treats dates as numbers. For example, number 1 is 1/1/1900 and 1/1/2000 is 36526. 1/2/2000 is 36527. The numbers you see above in the array are dates.
The WEEKDAY function converts the dates to their corresponding weekdays in numbers. 1 is Sunday and 7 is Saturday.
WEEKDAY({39814; 39815; 39816; 39817; 39818; 39819; 39820; 39821; 39822; 39823; 39824; 39825; 39826; 39827; 39828; 39829; 39830; 39831; 39832; 39833; 39834; 39835; 39836; 39837; 39838; 39839; 39840; 39841; 39842; 39843; 39844})=2
becomes
{5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7}=2
To identify Mondays in this array I will compare the numbers with 2.
{5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7}=2
returns the following boolean array:
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}
Boolean values are TRUE and FALSE.
Step 3 - Return corresponding date if Monday
The IF function lets you evaluate a logical expression and if TRUE one thing happens and if FALSE another thing happens.
The logical expression in this example evaluates to TRUE if date is a Monday.
IF(WEEKDAY(B3+ROW(INDIRECT("$1:$"&DAY(EOMONTH(B3,0))))-1)=2,B3+ROW(INDIRECT("$1:$"&DAY(EOMONTH(B3,0))))-1,"")
becomes
IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE},B3+ROW(INDIRECT("$1:$"&DAY(EOMONTH(B3,0))))-1,"")
If the value is TRUE then return the corresponding date. Since months have different number of days we must build an array that takes that into account.
It works just like the array we created in step 1.
IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE},B3+ROW(INDIRECT("$1:$"&DAY(EOMONTH(B3,0))))-1,"")
becomes
IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE},{39814; 39815; 39816; 39817; 39818; 39819; 39820; 39821; 39822; 39823; 39824; 39825; 39826; 39827; 39828; 39829; 39830; 39831; 39832; 39833; 39834; 39835; 39836; 39837; 39838; 39839; 39840; 39841; 39842; 39843; 39844},"")
and returns
{""; ""; ""; ""; 39818; ""; ""; ""; ""; ""; ""; 39825; ""; ""; ""; ""; ""; ""; 39832; ""; ""; ""; ""; ""; ""; 39839; ""; ""; ""; ""; ""}
Step 3 - Filter third Monday
The SMALL function lets you return the third largest number in array because we are looking for the third Monday in a given month.
SMALL(IF(WEEKDAY(B3 +ROW(INDIRECT( "$1:$"&DAY( EOMONTH(B3, 0))))-1)=2, B3+ROW( INDIRECT("$1:$"&DAY( EOMONTH(B3, 0))))-1, ""), 3)
becomes
SMALL({""; ""; ""; ""; 39818; ""; ""; ""; ""; ""; ""; 39825; ""; ""; ""; ""; ""; ""; 39832; ""; ""; ""; ""; ""; ""; 39839; ""; ""; ""; ""; ""}, 3)
and returns 39832 in cell C3 which is the same as 1/19/2009 if formatted as a date in Excel.
Get Excel *.xlsx file
9. How to use the WEEKNUM function
What is the WEEKNUM function?
The WEEKNUM function calculates a given date's week number based on a return_type parameter that determines which day the week begins.
Table of Contents
1. Introduction
What is a week?
A week is a time unit used to represent the cycle of seven days in the Gregorian calendar. A week consists of seven consecutive days, typically beginning on Sunday, Monday or Saturday depending on country and calendar system.
Each week cycles through the seven days of the week - Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.
How many weeks in a year?
Week numbers range from 1 to 52 for most years. Some years have 53 weeks if the 1st week starts very early in the previous year.
Is a week the same in all parts of the world?
No, the definition and numbering of a week is not the same in all parts of the world.
There are some key differences:
- Start of week - Some calendars start the week on Sunday, others on Monday.
- Week numbering - The rules for when Week 1 begins in a year vary between calendars.
- ISO 8601 standard - This internationally recognizes Monday as start of week and has a consistent week numbering definition.
- Cultural variations - Some cultures or religions use different definitions of a week. For example, the Jewish calendar week starts on Sunday.
What is a week number?
A week number refers to the ordinal numbering of weeks in a calendar year.
ISO 8601 is the international standard for week numbering. Week numbers provide an alternative to the month/day system for identifying dates. They are commonly used in work/school calendars, schedules, timetables, payment periods, etc.
When does week numbers start?
Week 1 is the first week of the year that contains at least 4 days of the new year.
What is the difference between the WEKNUM function and the ISOWEEKNUM function?
ISOWEEKNUM follows the ISO 8601 international standard for week numbering, while WEEKNUM is based on a more localized definition.
ISOWEEKNUM always starts weeks on Monday and Week 1 is the first week with 4+ days in the new year. WEEKNUM can start weeks on Sunday or Monday based on system settings.
Region | First Day of Week | Week Numbering |
---|---|---|
Europe | Monday | ISO 8601 (Week 1 is first with 4+ days in new year) |
North America | Sunday | Other |
South America | Monday | ISO 8601 |
Asia | Monday or Sunday | Varies by country, ISO and others |
Middle East | Saturday | Varies by country |
Africa | Sunday or Monday | ISO 8601 or others |
Most regions follow ISO 8601 now but there are still some variations.
2. WEEKNUM function Syntax
WEEKNUM(serial_number,[return_type])
3. WEEKNUM function Arguments
serial_number | Required. A date. If you enter the date in the function then use the DATE function to calculate the correct serial_number. |
[return_type] | Optional. A number determining which day the week begins. If omitted, 1 is used. |
The following table shows how the WEEKNUM function defines a week if [return_type] argument is specified.
[return_type] | Week starts on | System |
1 | Sunday | 1 |
2 | Monday | 1 |
11 | Monday | 1 |
12 | Tuesday | 1 |
13 | Wednesday | 1 |
14 | Thursday | 1 |
15 | Friday | 1 |
16 | Saturday | 1 |
17 | Sunday | 1 |
21 | Monday | 2 |
You also have the option to choose which system to use, see the [return_type] argument.
System 1 | Week 1 is the week that contains January 1. |
System 2 | Week 1 is the week that contains the first Thursday of the year. (European week numbering system). |
4. WEEKNUM function example
Formula in cell D3:
5. Extract week ranges based on a given date range
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3.
The following array formula in cell C7 extracts weeks that begin on a Sunday:
Formula in cell B7:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Excel 365 dynamic array formula in cell B7:
Explaining formula in cell C7
Step 1 - Create array 1 to n
The INDEX function creates a cell reference with the same number of rows as there are dates in the date range.
The ROW function then converts the cell range to an array of corresponding row numbers.
ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))
becomes
ROW($A$1:INDEX($A$1:$A$1000, 40633-40569))
becomes
ROW($A$1:INDEX($A$1:$A$1000, 64))
becomes
ROW($A$1:$A$64)
and 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; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64}
Step 2 - Create dates within date range
The next step subtracts the array with 1 and adds the Excel date number.
$B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1)
becomes
$B$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; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64}-1)
becomes
$B$3+{0; 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; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63}
becomes
40569+{0; 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; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63}
and returns
{40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603; 40604; 40605; 40606; 40607; 40608; 40609; 40610; 40611; 40612; 40613; 40614; 40615; 40616; 40617; 40618; 40619; 40620; 40621; 40622; 40623; 40624; 40625; 40626; 40627; 40628; 40629; 40630; 40631; 40632}
Step 3 - Convert dates to weekdays
The TEXT function converts the dates to weekdays.
TEXT($B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "DDD")
becomes
TEXT({40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603; 40604; 40605; 40606; 40607; 40608; 40609; 40610; 40611; 40612; 40613; 40614; 40615; 40616; 40617; 40618; 40619; 40620; 40621; 40622; 40623; 40624; 40625; 40626; 40627; 40628; 40629; 40630; 40631; 40632}, "DDD")
and returns
{"Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"}
Step 4 - Extract all Sundays
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF(TEXT($B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "DDD")="Sat", $B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "")
becomes
IF({"Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"}="Sun", $B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "")
becomes
IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603; 40604; 40605; 40606; 40607; 40608; 40609; 40610; 40611; 40612; 40613; 40614; 40615; 40616; 40617; 40618; 40619; 40620; 40621; 40622; 40623; 40624; 40625; 40626; 40627; 40628; 40629; 40630; 40631; 40632}, "")
and returns
{""; ""; ""; ""; 40573; ""; ""; ""; ""; ""; ""; 40580; ""; ""; ""; ""; ""; ""; 40587; ""; ""; ""; ""; ""; ""; 40594; ""; ""; ""; ""; ""; ""; 40601; ""; ""; ""; ""; ""; ""; 40608; ""; ""; ""; ""; ""; ""; 40615; ""; ""; ""; ""; ""; ""; 40622; ""; ""; ""; ""; ""; ""; 40629; ""; ""; ""}
Step 5 - Find n-th Sunday within range
To be able to return a new value in a cell each I use the SMALL function to filter date numbers from smallest to largest.
SMALL(IF(TEXT($B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "DDD")="Sat", $B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), ""),ROWS($A$1:A2))
becomes
SMALL({""; ""; ""; ""; 40573; ""; ""; ""; ""; ""; ""; 40580; ""; ""; ""; ""; ""; ""; 40587; ""; ""; ""; ""; ""; ""; 40594; ""; ""; ""; ""; ""; ""; 40601; ""; ""; ""; ""; ""; ""; 40608; ""; ""; ""; ""; ""; ""; 40615; ""; ""; ""; ""; ""; ""; 40622; ""; ""; ""; ""; ""; ""; 40629; ""; ""; ""}),ROWS($A$1:A2))
becomes
SMALL({""; ""; ""; ""; 40573; ""; ""; ""; ""; ""; ""; 40580; ""; ""; ""; ""; ""; ""; 40587; ""; ""; ""; ""; ""; ""; 40594; ""; ""; ""; ""; ""; ""; 40601; ""; ""; ""; ""; ""; ""; 40608; ""; ""; ""; ""; ""; ""; 40615; ""; ""; ""; ""; ""; ""; 40622; ""; ""; ""; ""; ""; ""; 40629; ""; ""; ""}),2)
and returns 40580 formatted as 2/5/2011.
Step 6 - Return blank if error
The IFERROR function handles errors, it returns a specified value if formula returns an error. In this case it returns a blank "".
6. WEEKNUM function not working
The WEEKNUM function returns #NUM! error if:
- serial_number is out of range.
- [return_type] is out of range.
10. How to use the WORKDAY function
What is the WORKDAY function?
The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and nonholidays).
What is a workday?
A weekday that is not a weekend day or holiday. Typically Monday through Friday excluding any holiday dates.
Use the WEEKDAY function to determine if a date is a workday or a weekend day.
What is a holiday?
A day on which regular activities and work is suspended due to a cultural, religious or legal custom. Varies by country and region.
WORKDAY function Syntax
WORKDAY(start_date, days, [holidays])
WORKDAY function Arguments
start_date | Required. |
days | Required. Positive integer returns a date after the start_date (future) and negative integer returns a date before the start_date. |
[holidays] | Optional. A list of holiday dates. |
WORKDAY function not working
The WORKDAY function returns
- #VALUE! error value if start_date or [holidays] are not a valid date.
- #NUM error if start_date plus days argument returns an invalid date.
(Excel can't handle dates before 1/1/1900.)
Use the DATE function to create valid Excel dates.
Example:
What is an Excel date?
An Excel date is a serial number that Excel recognizes and can be filtered, sorted and used in other date calculations.
Excel dates are actually serial numbers formatted as dates, 1/1/1900 is 1 and 2/2/2018 is 43133. There are 43132 days between 2/2/2018 and 1/1/1900.
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.
WORKDAY function example
This example demonstrates how to use the WORKDAY function to calculate a future date with the condition of being a workday excluding weekend days and holidays.
The start date is shown in cell C3 in the image above. The number of working days days is 12 and the function returns 1/17/2018. The formula in cell B8 is shown below, note that no holidays has been specified.
Formula in cell B8:
The image below shows the start date and the weekdays. The count is entered below dates that are workdays.
Note that there are no numbers below weekend days because they are not workdays. Counting 12 days after the start date and we get 1/17/2018.
WORKDAY function example - Negative days argument and a holiday
This example demonstrates the WORKDAY function with a negative day argument which means it calculates a workday before the start date instead of after the start date.
The start date is shown in cell C3 in the image above. The number of working days days is -12 and the function returns 12/13/2017. The formula in cell B8 is shown below, note that holiday 12/25/2017 has been specified.
Formula in cell B9:
This example also shows no numbers below weekend days because they are not workdays, this applies to 12/25/2017 as well because it si specified as a holiday. Counting 12 days befoe the start date and we get 12/13/2017.
11. How to use the YEAR function
What is the YEAR function?
The YEAR function converts a date to a number representing the year in the date. The number is between 1900 and 9999.
Table of Contents
- YEAR function syntax
- YEAR function arguments
- YEAR function example
- Calculate year from date?
- How to extract the year from a text string
- YEAR function not working?
- Is date an Excel date?
- How to calculate years between dates?
- How to convert year to a date?
- Year and month
- Year and quarter
- Year as text
- Year and month to date?
- Year by week?
- The year begins on what weekday?
- Get Excel file
1. YEAR function syntax
YEAR(serial_number)
2. YEAR function arguments
serial_number - The date you want to extract the year out of.
Excel uses whole numbers as dates. January, 1 , 1900 is 1 and January, 1, 2000 is 36526. There are 36525 days between those two dates.
3. YEAR function example
Formula in cell C3:
4. How to calculate the year from a date?
The image above shows the YEAR function in cell C3, it calculates a 4-digit year number based on an Excel date (serial number).
Formula in cell C3:
5. How to extract the year from a text string
5.1 Date at the end of the text string
The image above demonstrates three different formulas that extract the year from a text string. Cell B3 is a text string with the four last characters being the year number.
Cell B4 has the year number between other characters both from the right and left. Cell C5 begins with the year numbers.
Formula in cell C3:
The RIGHT function extracts a given number of characters from a text string starting from right.
RIGHT(text, [num_chars])
text - B3
[num_chars] - 4
5.2 Date somewhere in the middle of the text string
Formula in cell C4:
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)
text - B4
start_num - 5
num_chars - 4
The formula in cell C5 returns 1911.
5.3 Date at the beginning of the text string
Formula in cell C5:
The LEFT function extracts a given number of characters from a text string starting from the left.
LEFT(text, [num_chars])
text - B5
[num_chars] - 4
The formula in cell C5 returns 1986.
6. YEAR function not working?
6.1 YEAR function returns #NAME! error
The YEAR function is misspelled in cell C3, Excel returns a #NAME! error.
6.2 YEAR function returns #VALUE! error
Cell B3 contains a text string that the YEAR function in cell C3 doesn't recognize as a date.
Formula in cell C3:
This formula extracts the year from the string.
Formula in cell C3:
6.3 YEAR function returns #NUM! error
Cell B3 contains a string of letters that the YEAR function doesn't recognize as an Excel date. It returns #NUM! error.
Formula in cell C3:
The following formula extracts the year from the string.
Formula in cell C3:
7. Is date an Excel date?
The image above demonstrates three different values, Excel dates are right-aligned by default, text and general values are left-aligned by default.
However, numbers are also right-aligned by default. This can be confusing.
To really make sure Excel recognizes an Excel date go to tab "Home" on the ribbon. Select the cell you want to examine, now check the "Number Format".
Make sure Excel sees the dates as valid dates, see the image below.
Why do you want dates to be Excel dates?
You can't sort dates as text and general values properly, also calculations can't be made to text dates.
8. How to calculate years between dates?
The image above demonstrates a formula that returns the number of years between two given dates.
Formula in cell C3:
You can't use the YEAR function to calculate this, YEAR(C3) - YEAR(B3) returns 3 (2019-2016 equals 3), however, the correct result is 2.
Read more about the DATEDIF function.
9. How to convert year number to a date?
The picture above demonstrates a formula that returns an Excel date based on a year number specified in cell B3.
Formula in cell D3:
The formula in cell B6 is identical as the formula in cell D3, however, the cell is formatted to only show the year number. Read the next section to find out how to format.
9.1 How to format Excel date to yyyy
- Select cell B6.
- Press CTRL + 1 to open the "Format Cells" dialog box, see the image above.
- Select "Custom"
- Type: yyyy
- Press with left mouse button "OK" button.
Cell B6 is an Excel date showing only the year as a number.
10. Format Excel date to year and month
The image above shows a worksheet with the same date 6/28/2025 in cells B3:B8, however, cells B4:B8 are formatted differently.
- Select cell containing an Excel date.
- Press CTRL + 1 to open the Format Cells dialog box, see the image above.
- Type: m/yyyy
- Press OK button to apply changes.
Cell B5 has this format: yyyy-m
Cell B6 has this format: yyyy-mm
Cell B7 has this format: mmm yyyy
Cell B8 has this format: mmmm yyyy
11. Format Excel date to year and quarter
The image above shows
I recommend the year first and then the quarter, it allows you to sort data.
Formula in cell C3:
Explaining formula in cell C3
Step 1 - Calculate number representing month
The MONTH function returns a number corresponding to a month in a year. January - 1, ... , December - 12.
MONTH(B3)
becomes
MONTH(B3)
and returns 6. June is the sixth month in a year.
Step 2 - Subtract with one
The minus sign lets you subtract numbers in an Excel formula.
MONTH(B3)-1
becomes
6-1 and returns 5.
Step 3 - Divide by 3
The forward slash character lets you divide numbers in an Excel formula.
(MONTH(B3)-1)/3
becomes
5/3 equals 1.666666666667.
Step 4 - Remove decimals
The INT function returns a whole number.
INT((MONTH(B3)-1)
becomes
INT(1.666666666667)
and returns 1.
Step 5 - Add 1
The plus sign lets you add numbers in an Excel formula.
INT((MONTH(B3)-1)/3)+1
becomes
1+1 equals 2.
Step 6 - Calculate year from an Excel date
YEAR(B3)
becomes
YEAR(45836)
and returns 2025.
Step 7 - Concatenate values
The ampersand character & lets you concatenate values in an Excel formula.
YEAR(B3)&" Q"&INT((MONTH(B3)-1)/3)+1
becomes
2025&" Q"&2
and returns 2025 Q2.
12. Year as text
Formula in cell C3:
9.1 Explaining formula in cell C3
Step 1 - Calculate year
YEAR(B3)
becomes
YEAR(45836)
and returns 2025.
Step 2 - Concatenate values
The ampersand character & lets you concatenate values in an Excel formula.
"The year is "&YEAR(B3)
becomes
"The year is "&2025
and returns "The year is 2025".
13. Year and month to date?
The formula demonstrated in cell B6 in the image above returns an Excel date based on the specified year and month in cells B3 and C3 respectively.
Formula in cell B6:
9.1 Explaining formula in cell C3
Step 1 - Calculate number corresponding to position
The MATCH function returns a number representing the position of a given month in a year. January - 1, ... , December - 12.
MATCH(C3, {"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, 0)
becomes
MATCH("February", {"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, 0)
and returns 2.
Step 2 - Calculate date
The DATE function creates an Excel date based on three arguments, year, month and day.
DATE(B3, MATCH(C3, {"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, 0), 1)
becomes
DATE(B3, 2, 1)
becomes
DATE(2024, 2, 1)
and returns 2/1/2024.
14. Year by week
The image above shows a formula in cell B3 that returns dates in a year by week. The formula in cell D3 also displays the week number.
Formula in cell B3:
Formula in cell D3:
10.1 Explaining formula in cell B3
Step 1 - Create number sequence 1 to n step 7
The ROW function calculates the row number of a cell reference.
ROW(reference)
ROW(A1)*7-6
becomes
1*7-6
becomes
7-6 equals 1.
Relative cell reference A1 changes automatically when you copy and paste the cell to cells below.
Step 2 - Create Excel date based on sequence
The DATE function creates an Excel date based on three arguments, year, month and day.
DATE(2025, 1, ROW(A1)*7-6)
becomes
DATE(2025, 1, 1)
and returns 1/1/2025.
15. The year begins on what weekday?
The image above shows a formula in cell D3 that returns the weekday of the first day in a given year, cell B3 specifies the year.
Formula in cell D3:
10.1 Explaining formula in cell B3
Step 1 - Create an Excel date of the first day in a given year
The DATE function creates an Excel date based on three arguments, year, month, and day.
DATE(B3, 1,1)
becomes
DATE(2021, 1,1)
and returns 44197 (1/1/2021).
Step 2 - Return weekday of the given date
The TEXT function converts a value to text in a specific number format.
TEXT(value, format_text)
TEXT(DATE(B3, 1,1), "ddddd")
becomes
TEXT(44197 , "ddddd")
and returns Friday.
16. Get Excel file
12. How to use the YEARFRAC function
What is the YEARFRAC function?
The YEARFRAC function returns the fraction of the year based on the number of whole days between a start date and an end date. This is made possible because of how Excel handle dates.
What is an Excel date?
An Excel date is a serial number that Excel recognizes and can be filtered, sorted and used in other date calculations.
Excel dates are actually serial numbers formatted as dates, 1/1/1900 is 1 and 2/2/2018 is 43133. There are 43132 days between 2/2/2018 and 1/1/1900.
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 year?
A period of 365 days (366 days in leap years) based on the revolution of the Earth around the Sun. It represents one complete cycle of seasons - spring, summer, autumn, winter. Calendar years are numbered and divided into 12 months.
What is a leap year?
A leap year is a 366 day year occurring every 4 years to maintain calendar accuracy. A year containing an extra day, February 29th, that occurs once every 4 years.
The leap year was added to the Gregorian calendar to keep it aligned with the astronomical year. It occurs in years divisible by 4, except centurial years not divisible by 400.
What is a fraction?
A fraction has a numerator and a denominator:
- Numerator - The top number in a fraction.
- Denominator - The bottom number.
For example:
6/7
- The numerator is 6, meaning 6 parts.
- The denominator is 7, meaning the whole was split into 7 equal parts.
YEARFRAC function Syntax
YEARFRAC(start_date, end_date, [basis])
YEARFRAC function Arguments
start_date | Required. |
end_date | Required. |
[basis] | Optional. Day count basis.
0 - Default value. US (NASD) 30/360 1 - Actual/actual 2 - Actual/360 3 - Actual/365 4 - European 30/360 |
YEARFRAC function example
Use the YEARFRAC function to calculate a part of a whole based on a date range relative to a year. This example demonstrates how to calculate the fraction of a year based on two specific dates located in cells C3 and C4 respectively. The [basis] argument is 0 (zero) in this example meaning it uses 30 days per month and a year has 360 days.
Formula in cell B8:
The YEARFRAC function in cell B8 returns 0.4166666 or 5/12 in fractions.
YEARFRAC function tips
Use the DATE function to create a date if you enter the date in the function.
Example:
The YEARFRAC function requires Excel dates to work properly, the DATE function allows you to create Excel dates by specifying the year, month, and day.
YEARFRAC function not working
If using the US (NASD) 30/360 basis which is argument 0 (zero), and the start_date argument is the last day in February the YEARFRAC function may return an incorrect result.
The YEARFRAC function may return an incorrect result if dates are entered as text values.
YEARFRAC returns
- the #VALUE! error value if start_date or end_date arguments are not valid dates.
- the #NUM! error value if basis < 0 or if basis > 4
Excel 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