Month Day, Day Month

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic




See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials

Who's counting?

2004 has 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.

Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.

Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.

Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

In Access:
DatePart Function

If your work week is always Saturday through Friday then

datepart("ww",[DateField],7,1)

will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.

The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.



See all Topics
excel

Labels: Formats, Functions, Tips

Zero is nothing

• Display or hide all zero values on a worksheet
  
  
• Use a number format to hide zero values in selected cells
  
  
• Use a conditional format to hide zero values returned by a formula
  
  
• Use a formula to display zeros as a blanks or dashes
  
  
• Hide zero values in a PivotTable report

Labels: Formulas, Functions, Tips

The drunken cousin

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

Here's THE source for date math:
Chip Pearson:
All About Dates

Also:

John Walenbach:
Extended Date Functions Add-In

"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."

Labels: Formulas, Functions, Tips

Basic tutorial

"This practical spreadsheet lesson offers easy answers to life's perplexing math problems like How much will my dream car really cost after financing?

Students will calculate the cost of purchasing their very own Lamborghini sports car and determine if the ultimate price tag is really worth the investment. "

Dream Car

Also:

Loan templates
Basic Financial Calculations



See all Topics
excel

Labels: Functions, Tutorials

Using the OFFSET function

"How to create a data list to manage transactions, add and delete rows from the list, and accurately calculate a running balance using the OFFSET function."

Cash flow using OFFSET

Office.microsoft.com:
Calculate a running total

Using Auto Calculate for Running Totals



See all Topics
excel

Labels: Formulas, Functions

Cardinal numbers

From the Microsoft Knowledgebase collection:


How to Convert a Numeric Value into English Words - 213360

This support article contains the VBA code needed to create a User Defined Function, You can, then, change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering something like the following formula into a cell:

=SpellNumber(32.50)


Also:

From John Walkenbach:

BAHTTEXT?
If you use Excel 2002+, you may have discovered the new BAHTTEXT worksheet function. This function converts a number to Thai text, and adds a suffix of "Baht".
This is the only built in function that translates numbers to words and then only in Thai.

For Access see:
Access- Numbers to Words


To create cardinal numbers in Word, see:
Word - Numbers to Words



See all Topics
excel

Labels: Functions, VBA

Info source

"What is the Excel Wiki?

That is really up to the community! Right now, the message board is a pull technology. Someone asks a question and then someone answers it. My idea... allow the community to write articles on their favorite tips about Excel. In theory, we would eventually have an article about every Excel function and every Excel command.

Currently, there are sections for Commands, Functions, VBA, and Tips. If you think there should be a new section, feel free to edit the ExcelWiki and add a link to a new section."

Labels: Functions, Tips

Definitions and list

SKEW(number1,number2,...)

Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean.

Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values.

Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.

Labels: Functions