Date function

DateDif

To get the number of days, weeks or years between two dates in Excel, use the DATEDIF function. The DATEDIF function has three arguments.

1. Fill in "d" for the third argument to get the number of days between two dates.

Note: =A2-A1 produces the exact same result!

2. Fill in "m" for the third argument to get the number of months between two dates.

3. Fill in "y" for the third argument to get the number of years between two dates.

4. Fill in "yd" for the third argument to ignore years and get the number of days between two dates.

5. Fill in "md" for the third argument to ignore months and get the number of days between two dates.

6. Fill in "ym" for the third argument to ignore years and get the number of months between two dates.

Important note: the DATEDIF function returns the number of complete days, months or years. This may give unexpected results when the day/month number of the second date is lower than the day/month number of the first date. See the example below.

The difference is 6 years. Almost 7 years! Use the following formula to return 7 years.

Weekday Function

1. The WEEKDAY function in Excel returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. Apparently, 12/16/2013 falls on a Monday.

2. You can also use the TEXT function to display the day of the week.

3. Create a custom date format (dddd) to display the day of the week.

Networkdays Function

1. The NETWORKDAYS function returns the number of weekdays (weekends excluded) between two dates.

2. If you supply a list of holidays, the NETWORKDAYS function returns the number of workdays (weekends and holidays excluded) between two dates.

The calendar below helps you understand the NETWORKDAYS function.

3. Dates are stored as numbers in Excel and count the number of days since January 0, 1900. Instead of supplying a list, supply an array constant of the numbers that represent these dates. To achieve this, select E1:E2 in the formula and press F9.

Workday Function

The WORKDAY function is (almost) the opposite of the NETWORKDAYS function. It returns the date before or after a specified number of weekdays (weekends excluded).

Note: the WORKDAY function returns the serial number of the date. Apply a Date format to display the date.

The calendar below helps you understand the WORKDAY function.

Again, if you supply a list of holidays, the WORKDAY function returns the date before or after a specified number of workdays (weekends and holidays excluded).

Days until Birthday

To calculate the number of days until your birthday in Excel, execute the following steps.

1. Select a cell and enter your date of birth.

2. Select the cell next to it and enter the TODAY function to return today's date.

3. The most difficult part in order to get the number of days until your birthday is to find your next birthday. The formula below does the trick.

Explanation: The DATE function accepts three arguments: year, month and day. We used the DATEDIF function to find the number of complete years ("y") between Date of Birth and Today. DATEDIF(A2,B2,"y") equals 32. If 32 complete years have passed since your date of birth (in other words, you have already celebrated your 32st birthday), your next birthday will be 32 + 1 = 33 years after your date of birth.

4. Next, we use the DATEDIF function to find the number of days ("d") between Today and Next Birthday.

Time Sheet

This example teaches you how to create a simple timesheet calculator in Excel. Cells that contain formulas are colored light yellow. If you are in a hurry, simply download the Excel file.

1. To automatically calculate the next 4 days and dates when you enter a start date, use the formulas below.

2. Select the cells containing the times.

3. Right click, click Format Cells, and select the right Time format. Use the circled format for cell K12, K13, K14.

4. To automatically calculate the hours worked each day, the total hours and the overtime hours, use the formulas below.

Last Day of the Month

To get the date of the last day of the month in Excel, use the EOMONTH (End of Month) function.

1. For example, get the date of the last day of the current month.

Note: the EOMONTH function returns the serial number of the date. Apply a Date format to display the date.

2. For example, get the date of the last day of the next month.

3. For example, get the date of the last day of the current month - 8 months = 6 - 8 = -2 = October (-2+12=10), 2011!

Holidays

This example teaches you how to get the date of a holiday for any year. If you are in a hurry, simply download the Excel file.

Before you start: the CHOOSE function returns a value from a list of values, based on a position number. For example, =CHOOSE(3,"Car","Train","Boat","Plane") returns Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date.

1. This is what the spreadsheet looks like. If you enter a year into cell C2, Excel returns all the holidays for that year. Of course, New Year's Day, Independence Day, Veteran's Day and Christmas Day are easy.

2. All other holidays can be described in a similar way: the xth day in a month (except Memorial day which is slightly different). Let's take a look at Thanksgiving Day 2016. If you understand Thanksgiving Day, you understand all holidays. Thanksgiving is celebrated the 4th Thursday in November.

The calendar below helps you understand Thanksgiving Day 2016.

Explanation: DATE(C2,11,1) reduces to 11/1/2016. WEEKDAY(DATE(C2,11,1)) reduces to 3 (Tuesday). Now the formula reduces to 11/1/2016 + 21 + CHOOSE(3,4,3,2,1,0,6,5) = 11/1/2016 + 21 + 2 = 11/24/2016. We needed the 2 extra days because it takes 2 days until the first Thursday in November. From there, it takes another 21 days (3 weeks) until the 4rd Thursday in November. It doesn't matter on which day November 1 falls, the CHOOSE function correctly adds the number of days until the first Thursday in November (notice the pattern in the list of values). From there, it always takes another 21 days until the 4rd Thursday in November. Therefore, this formula works for every year.

3. Let's take a look at Martin Luther King Jr. Day.

Explanation: this formula is almost the same. The first DATE function reduces to the first of January this time. The base position (0) in the list of values for the CHOOSE function is located at the second spot now (we are looking for a Monday). If the 1st of January falls on a Monday, it takes 14 + CHOOSE(2,1,0,6,5,4,3,2) = 14 + 0 = 14 days (2 weeks) until the 3rd Monday in January.

Quarter

An easy formula that returns the quarter for a given date. There's no built-in function in Excel that can do this.

1. Enter the formula shown below.

Explanation: ROUNDUP(x,0) always rounds x up to the nearest integer. The MONTH function returns the month number of a date. In this example, the formula reduces to =ROUNDUP(5/3,0), =ROUNDUP(1.666667,0), 2. May is in Quarter 2.

2. Let's see if this formula works for all months.

Explanation: now it's not difficult to see that the first three values (months) in column B are rounded up to 1 (Quarter 1), the next three values (months) in column B are rounded up to 2 (Quarter 2), etc.

Day of the Year

An easy formula that returns the day of the year for a given date. There's no built-in function in Excel that can do this.

1. Enter the formula shown below.

Explanation: Dates and times are stored as numbers in Excel and count the number of days since January 0, 1900. June 23, 2012 is the same as 41083. The DATE function accepts three arguments: year, month and day. DATE(YEAR(A1),1,1) or 1-jan-2012 is the same as 40909. Subtracting these numbers (41083 - 40909 = 174) and adding 1 gives the day of the year.

Text Functions

Excel has many functions to offer when it comes to manipulating text strings.

Join Strings

To join strings, use the & operator.

Note: to insert a space, use " "

Left

To extract the leftmost characters from a string, use the LEFT function.

Right

To extract the rightmost characters from a string, use the RIGHT function.

Mid

To extract a substring, starting in the middle of a string, use the MID function.

Note: started at position 5 (p) with length 3.

Len

To get the length of a string, use the LEN function.

Note: space (position 8) included!

Find

To find the position of a substring in a string, use the FIND function.

Note: string "am" found at position 3.

Substitute

To replace existing text with new text in a string, use the SUBSTITUTE function.

Separate Strings

This example teaches you how to separate strings in Excel.

The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9.

1. To get the first name, use the formula below.

Explanation: to find the position of the comma, use the FIND function (position 6). To get the length of a string, use the LEN function (11 characters). =RIGHT(A2,LEN(A2)-FIND(",",A2)-1) reduces to =RIGHT(A2,11-6-1). =RIGHT(A2,4) extracts the 4 rightmost characters and gives the desired result (Mike).

2. To get the last name, use the following formula.

Explanation: to find the position of the comma, use the FIND function (position 6). =LEFT(A2,FIND(",", A2)-1) reduces to =LEFT(A2,6-1). =LEFT(A2,5) extracts the 5 leftmost characters and gives the desired result (Smith).

3. Select the range B2:C2 and drag it down.

Number of Instances

This example describes how to count the number of instances of text (or a number) in a cell.

1. Use the LEN functon to get the length of the string (25 characters, including spaces).

2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,"")) equals 13 (the length of the string without the words dog). If we subtract this number from 25, we get the length of the dog instances (25-13=12).

3. Dividing this number by the length of the word dog (3), gives us the dog instances (12/3=4).

Number of Words

This example describes how to count the number of words in a cell.

1a. The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed.

1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function.

2a. The SUBSTITUTE function replaces existing text with new text in a text string. We use the SUBSTITUTE function to get the string without spaces.

2b. To get the length of the string without spaces, we combine the LEN and SUBSTITUTE function.

3. Now comes the simple trick. To get the number of words, we subtract the length of the string without spaces (10) from the length of the string with normal spaces (12) and add 1.

Text to Columns

To separate the contents of one Excel cell into separate columns, you can use the 'Convert Text to Columns Wizard'. For example, when you want to separate a list of full names into last and first names.

1. Select the range with full names.

2. On the Data tab, click Text to Columns.

The following dialog box appears.

3. Choose Delimited and click Next.

4. Clear all the check boxes under Delimiters except for the Comma and Space check box.

5. Click Finish.

Note: This example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.

Result:

Lower/Upper Case

This example teaches you how to convert a text string to lower, upper or proper case in Excel.

1. Use the LOWER function to convert all letters in a text string to lowercase.

2. Use the UPPER function to convert all letters in a text string to uppercase.

3. Use the PROPER function to convert a text string to proper case. That is, the first letter in each word in uppercase, and all other letters in lowercase.

Remove Unwanted Characters

The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. The CLEAN function removes nonprintable characters from a string.

1. For example, cell A1 below contains the string " Excel Easy "

Note: the TRIM function returns the string "Excel Easy".

2. For example, cell A1 below contains a nonprintable character.

Note: the CLEAN function removes this nonprintable character. Text imported from other applications may contain nonprintable characters.

Compare Text

This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive.

1. Use the EXACT function (case-sensitive).

2. Use the formula =A1=B1 (case-insensitive).

Find vs Search

The FIND function and the SEARCH function are very similar to each other. This example shows the difference.

1. To find the position of a substring in a string, use the FIND function. FIND is case-sensitive.

2. To find the position of a substring in a string, use the SEARCH function. SEARCH is case-insensitive.

Note: string "excel" found at position 11. Even though it's actually the string "Excel"

3. The SEARCH function is more versatile. You can use wildcard characters when you use this function.

Note: A question mark (?) matches exactly one character. An asterisk (*) matches a series of zero or more characters.

4. One other interesting point to mention about the FIND and the SEARCH function is that they have a 3rd optional argument. You can use this argument to indicate the position, counting from the left, at which you want to start searching.