Category: excel

When working with dates in Excel, you can get the number of days between two dates using the DAYS() or NETWORKDAYS() formulas.

• DAYS() will return the total number of days, including weekends.
• NETWORKDAYS() removes weekends and only gives you the count of only the business/week days between two dates.

You could use DAYS() to quickly get total time passed between two dates such as:

• How many days or years (divide by 365) an employee has been on staff
• How much time passed between ticket creation and resolution
• How much time was spent traveling

Whereas NETWORKDAYS() can be used to get metrics such as:

• How many business days were spent on a project
• How many week days are available for allotment to a future project
• How many school days are in the year

The structure of the formulas and their arguments are as follows:

DAYS() = end date, start date (i.e. =DAYS(End, Start) -or- =DAYS(D2,C2))

NETWORKDAYS() = start date, end date, holidays to exclude (i.e. =NETWORKDAYS(Start, End, Sheet2!B2:B8) -or- =NETWORKDAYS(C2,D2,Table1[Date]))

I’ve created an example workbook that demonstrates both of these formulas in action. You can download the example here.

Note: For NETWORKDAYS, I placed the holidays table on a separate sheet named Holidays. This allows me to add and remove rows easily for more or fewer holidays without needing to adjust my formulas. You could, alternatively, just use a range of dates (i.e. Holidays!B2:B8) whether or not it’s part of a table but that could result in incorrect information if you don’t also update your formulas to include additional holiday rows when added.

Get business days between dates in Excel with modified weekend parameters

If your weekends aren’t Saturday/Sunday, or you just want to exclude certain days of the week from your calculations, you can alternatively use NETWORKDAYS.INTL instead of NETWORKDAYS. This adds an additional argument after end date and before holidays that allows you specify which day(s) to exclude as the weekends.

NETWORKDAYS.INTL() is structured as follows: start date, end date, weekends, holidays. The following screenshot shows the options available for the weekend argument:

I love learning new things. Today I was using a CSV export of data in Excel and was just using Excel to reassemble the data. At one point it resembled something like:

This is, of course, after some manipulation. Basically I removed the parts of a URL column expanded by delimiter (“/”) I didn’t want. This left awkward gaps and all I wanted was to quickly delete the gaps and shift contents of remaining cells to the left to sort of “reconstruct” each row without gaps. Since they didn’t line up in neat columns, I needed a different method.

I found a great article that helped. To summarize the steps:

1. Select the range for which you’ll delete blank cells and shift data left.
2. Press Ctrl+G.
3. Click Special… (lower left of dialog)
4. Choose the Blanks radio button
5. Click OK.
6. All blank cells in the selected range remain highlighted. Now right-click any of the selected blank cells.
7. Choose Delete.
8. Select Shift cells left.
9. Click OK.

Here’s an animated GIF showing the process on my example data:

Since Excel doesn’t have the same set of robust text transformation tools as Word, we need creative ways to get the same effects sometimes. When it comes to auto-capitalizing whole words or first letters of words, there are two functions you can use:

• =UPPER() will capitalize the entire cell contents
• =PROPER() will capitalize first letters of each word in a cell

In the GIFs below, you’ll notice I demonstrate both. The steps for both are exactly the same, so just pick the formula/function you need.

Since nobody wants to add another column to a sheet unnecessarily, we’ll just temporarily create a column for the calculation, copy the result, then delete it. You may, alternatively, wish to just hide it for later use or keep it alongside the original for a purpose.

Insert column and formula

1. Insert column
2. Place formula of choice in top cell of column and drag down (lower right-hand box/handle of cell) or press enter if in table format as seen below

Copy new values over originals and delete temporary column

Next we’ll use the “Paste values” feature to remove the formula dependency of the resulting data and keep just the newly formatted text.

1. Copy values
2. Paste (special) values only over original column values to replace them with the correct capitalization version
3. Delete temporary column

This morning I pasted a list of values into Excel in which the pattern consisted of a “good value” followed by that same value with a minor adjustment. The second, near-duplicate value wasn’t useful to me so I wanted to filter out all even rows so I’d just have the first of each near-duplicate.

I say near-duplicate because if your values truly are duplicates, you can just use the Remove Duplicates function.

So to filter odds and evens, we’ll add a new column to the right of our data and set the formula to:

=ISEVEN(ROW())

This evaluates the data’s row number as even or odd and will return TRUE if the number is even or FALSE if not. Double-click the handle in the lower right of the cell to fill down.

Then we filter the column for just TRUE (evens) or FALSE (odds). You can right-click the value you want to keep > Filter > Filter by Selected Cell’s Value