WORKDAY Function

The WORKDAY function is similar to the NETWORKDAYS function.  It differs in that it calculates a date X number of days from the Start_date.  The "target" date can be either before or after the Start_date.  This function is frequently used the Start or End date for a project.

The syntax for the WORKDAY function is: =WORKDAY(Start_day, Days, Holidays)

Where:
"Start_date" = The start date of the period to be calculated
"Days" = The number of days for the period
"Holidays" = The date or range of dates to be excluded from the Period.  The "Holidays" argument is optional.


In the post for NETWORKDAYS I said that "The data could be entered into a table similar to the one here."  Actually, only the list of Holidays is typically entered in a table like this and is usually on a hidden sheet.  The "Start_date" and "Days" are entered as arguments in the formula wherever the function is being used in the workbook.


As with NETWORKDAYS the Holiday argument is optional and is not limited to holidays but can be used for any day to be excluded from the calculation.


If the function is to be used to calculate the beginning date rather than the ending date the "Days" argument is entered as a negative.

NETWORKDAYS Function

Requests for various workbooks have come in over the years that included the need to determine the number of billing or invoicing days in the period selected by the user.  If the company invoices every workday then this can be done using the NETWORKDAYS function.  This function counts the number of days between two dates inclusive and excludes weekends.  Holidays and/or any specified day off can also be excluded.

The syntax for the NETWORKDAYS function is:
=NETWORKDAYS(Start_date, End_date,Holidays)

Where:
"Start_date" = The first day of the selected Period
"End_date" = The last day of the selected Period
"Holidays" = The date or range of dates to be excluded from the Period.  The "Holidays" argument is optional.


The data could be entered into a table similar to the one here.  This would usually be done on a hidden worksheet



If =NETWORKDAYS(B1, B2, B4:B11) is entered using the data above the result will be 22.  This result is then available for other calculations in the workbook.

NOTE:  If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

Excel's Convert Function

I frequent John Walkenbach's website The Spreadsheet Page (spreadsheetpage.com) and always learn something new.  Today I downloaded a workbook that lists all the functions in Excel 2007.  There are 340 functions in the list, most of which I've never used and CONVERT is one that I'd never used or even heard of.

The CONVERT function "Converts a number from one measurement system to another" and is listed in the Engineering Category of Functions.  Using this function you can easily convert miles to kilometers,  pounds to kilograms, degrees Celsius to degrees Fahrenheit and many others.

I've already found a practical use for this function.  Using it I found that a 1.5 Liter bottle of Bourbon contains 33.8 1.5 oz shots.

You can learn more about this function at
http://office.microsoft.com/client/helppreview.aspx?AssetID=HP100623111033&QueryID=03RAPgEGJ&respos=1&rt=2&ns=EXCEL&lcid=1033&pid=CH100645391033

Here's a link to the Microsoft Excel 2007 Help list of 2007 functions
http://office.microsoft.com/client/helppreview.aspx?AssetId=HA102775249990&lcid=1033&NS=EXCEL&Version=12&pid=CH010064502&CTT=4#BM1

Useful Shortcut Keys

Here are some shortcut keys that I frequently use.

Ctl+ Arrow keys
Select a cell in a range of data
The Ctl key + the down Arrow key will take you from the active cell to the last populated cell in the column
The Ctl key + the up Arrow key will take you from the active cell to the first populated cell in the column
The Ctl key + the right Arrow key will take you from the active cell to the last populated cell in the row
The Ctl key + the left Arrow key will take you from the active cell to the first populated cell in the row

Ctl + Shift + the Arrow keys as above will select all cells from active cell to the last populated cell.  The selection of a range of cells allows you to copy, cut, format etc.

Ctl + C copies the active cell

Ctl + V pastes whatever has been copied to the active cell

Ctl + Z  undo last action

Ctl + A select all cell in current region

Ctl + F find

Selection using ALL, Division, Region, Branch

I've tried several methods of allowing users to make selections of ALL, Region and Branch and came up with a  way of doing it but only if the Branches assigned to a Region do no overlap.  This has it's obvious limitations and will definitely not work for Customer with a large number of Branches.  I was discussing this in one of the Excel classes I was conducting at our User Group meeting in Las Vegas and was asked why I didn't just utilize the available fields in the Branch Item Master file to make this selection.  I had thought of this solution previously but could not determine how it could be used.  This was because I was limiting myself to using a range of values in my SQL statement.  Once I began to consider other ways of accomplishing the goal it seems that the solution came very quickly.  Rather than using a range of values it was only necessary to use the OR statement.  The statement works like this..... Company = value OR Division = value OR Region = value OR Branch = value.  When the parameters values are passed to the MS Query  only one is valid, the others are invalid.  Such a simple solution and so easy to implement.