How to create list of business days in Excel 2013

The easiest way to generate a list of business days is using following functions:

1. WORKDAY

2. WORKDAY.INTL

Generating list of business days using WORKDAY:

The WORKDAY function returns the serial number of the date before or after a specified number of workdays.

Syntax:               =WORKDAY(start_date, days, holidays)

• Start_date: Needs the input of the start date. This could be directly entered or can be given as cell reference.
• Days: This is the number of non-weekend and non-holiday days before or after the start-date
• Positive value is used to get date after the start-date
• Negative value is used to get number before the start-date
• Holidays: This is an optional argument wherein you can feed the list of additional holidays.

Notes:

• Excel stores the dates as sequential serial numbers for the ease of calculations (by default 1 January 1900 is stored as 1.
• In order to view the number as date, select the cell and follow as below:
• Right click >> Format cells >> "Number" tab >> Select "Date" in Category
• If any of the input date is not a valid date, the function will return #VALUE! error.
• If the output returned is an invalid date, the function returns #NUM! error.

Generate list of business days using WORKDAY.INTL function:

This function is only available in Excel 2010 and 2013 versions.

Syntax:               =WORKDAY.INTL(start_date, days, [weekend], [Holidays])

As you would have noticed from above syntax, the only addition is [weekend] as compared to WEEKDAY function syntax.

The two functions are pretty much the same except the WORKDAY.INTL function allows you to specify when your weekend dates are.

Note:

The default weekend is Saturday and Sunday. Hence, if the desired weekend is same, then you can keep this argument blank.

FIND and SEARCH text functions in Excel 2013

FIND

FIND(find_text,within_text,start_num)

FIND checks for a substring within another text string and tells us how many characters along it appears from left (for the instance that it finds first).

The substring you want to find is entered into "find_text", while the text string from which it needs to find is entered into "within_text"

The input text strings can be a direct input inside "" or by giving a cell reference.

The third input "start_num" is optional. It gives flexibility to tell it how many characters in to start.

FIND function is case-sensitive.

SEARCH

SEARCH(find_text,within_text,start_num)

The syntax and working of SEARCH function is exactly same as FIND function. The advantage with SEARCH function is that it is case-insensitive.

We can use wildcard characters inside SEARCH function and hence it is more versatile than FIND function.

E.g. Wildcard characters like question mark (?) matches exactly one character, while an asterisk (*) matches a series of zero or more characters.

The below example shows the use of "?" wildcard character. It tells the SEARCH function to find the first string that starts with a D and ends with a R with any five letters in between denoted by "?????". Thus, it will return the number of character from where the first string meeting with above criteria starts i.e. 24.

PROPER, UPPER and LOWER text functions in Excel 2013

PROPER

PROPER(text)

PROPER function converts a text string to proper case i.e. the first letter in each word in uppercase and all other letters in lowercase.

UPPER

UPPER(text)

UPPER function converts all the letters in specified text into uppercase.

The input text string can be a direct input inside "" or by giving a cell reference.

LOWER

LOWER(text)

Yes, you guessed it correctly. LOWER function converts all the letters in specified text into lowercase.

The input text string can be a direct input inside "" or by giving a cell reference.

LEFT, RIGHT, MID and LEN text functions in Excel 2013

LEFT

left(text, num_chars)

As the name suggests, LEFT function extracts a part of text string from the left side of a given string. The syntax takes the input string as "text" and number of characters to be extracted as "num_chars".

RIGHT

right(text, num_chars)

RIGHT function does exactly same thing as LEFT but it extracts text from extreme right.

MID

MID(text, start_num, num_chars)

MID function is slightly more sophisticated. This function extracts string from the middle of the input text. It needs starting point count "start_num" which it considers from left side of the text and then extracts number of charters defined by "num_chars"

LEN

LEN(text)

This function returns the number of characters in a text string. The input text string can be a direct input inside "" or by giving a cell reference.

Text alignment keyboard shortcuts for Excel 2013

Text alignment options are located at below location in Excel 2013:
"HOME" tab>>>"Alignment" area

Both vertical and horizontal alignment options are available in the area:

Vertical Alignment: Top, Middle, Bottom
Horizontal Alignment: Left, Center, Right

On pressing "Alt" key, the shortcut key for tabs on the ribbon gets activated

"Alt+H" will activate the "Home" tab in the ribbon and shortcut letters of this tab will get activated.

Thus, the letters mentioned below along with "Alt+H" will be shortcut keys for respective text alignment:

Vertical Alignment shortcuts:
AT-Align Top

AM-Align Middle

AB-Align Bottom

Also read: How to Trace Precedents and Dependents in Excel
Horizontal Alignment shortcuts:
AL-Align Left

AC-Align Center

AR-Align Right

Also read: How to Freeze Panes in Excel 2013

Once the alignment options are applied, the active alignment of the active cell can be seen in the alignment area as below:

More interesting articles on this website:

• Two-way Lookup in Excel 2013 using MATCH and INDEX
• How to create Histogram in Excel 2013
• How to insert current date and time in a cell in Excel 2013