Consulting | Excel Examples
Number of Days, Months and Years Between Two Dates
You can create a Visual Basic for Applications User Defined Function to return the number of years, months and days from a start date to an end date. Use the Function in formulas or to create a text string: "As of yy/yy/yy, nn Years, mm Months and pp Days have elapsed from the start date xx/xx/xx".
User Defined Function for Week Number
You can use the "ww" value of the Format function to return week number. This value is not available in the user interface. If you include it in a User Defined Function, you can use it to calculate week number in worksheet formulas.
Entering Time Without the Colon
You can use an OnEntry procedure to speed entry of time values. Each time you enter data in a cell, a VBA procedure can be run which converts the entry to a valid time value. For example, you can enter the value "1300" and the VBA procedure will convert it to 1:00PM.
Using Built-In Workday Calculation Functions
You can use Excel's built in Workdays and Networkdays functions to perform calculations with dates taking into account weekends and holidays. Workday returns a date some number of workdays in the future. Networkdays calculates the number of workdays between two dates.