To calculate the number of months between two dates, there are two possibilities:
1. Both dates occur in the same year.
2. Dates occur in two different years.
Case 1 - both dates occur in the same year
Use the MONTH function to extract the month from each date, then subtract the two months from each other.
Example (using format:dd/mm/yyyy):
Suppose [...]
To calculate the difference or the number of days between two dates, you simply subtract one date from the other.
Example (using format:dd/mm/yyyy):
Suppose you have the date 25/11/2007 in cell A1 and the date 15/01/2008 in cell B1 (both cells formatted as date). To calculate the difference between these two dates you use the following formula:
=B1 [...]
I have added to the downloads page a yearly calendar template. This is a dynamic template that will allow you to generate a calendar for any year between 2001 and 2016. There are two combo boxes for the user to select the year and the start date of the week. Each selection will trigger a [...]
To get the week number of a given date, i.e. to find out where the week falls numerically within a year you use the WEEKNUM function.
Syntax
WEEKNUM(serial_num,return_type)
serial_num : is a valid date. E.g. August 15, 2008 or 12/10/2008.
Return_type : is a number that determines on which day the week begins. (1 = Sunday, 2 = Monday). [...]
To count cells, within a given range or list of arguments, that contains only numbers you use the COUNT function.
Dates and text that represent numbers are considered numbers and will therefore be counted.
Logical values, text, or error values are not counted
Syntax
COUNT(value1,value2,…)
Examples:
COUNT(A1:A20) :counts numbers in a range of cells.
COUNT(A1:A20, 7) :counts numbers in a range [...]
If you want to format a date with month in upper case, then hear is how you do it:
1. The general format for getting the month in three character is this:
=TEXT(cell_reference,”mmm”)
2. To get upper case you enclose the TEXT function within the UPPER function as follows:
=UPPER(TEXT(cell_reference,”mmm”))
So if we have a date in cell A1 for [...]
The MATCH function searches for an item (lookup value) in a given array or a data range (lookup array), and if I understand it right the array can only be within one column or within one row but I could not find that mentioned explicitly in any reference. When the item is found, MATCH returns [...]
Excel has a built in function which returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default, or 1 (Monday) to 7 (Sunday) if you use a return type of 2. But there is no direct function to return [...]
The following Visual Basic code is for a function to determine whether a certain year is leap year or not. It will receive the year as an argument and return TRUE for a leap year and FALSE for others.Copy this code and paste it in a new visual basic module.
Function IsLeap(iYear)
If (iYear Mod 400) = [...]
Excel provides a vast number of common date formats. However if you do need a format that is not included in the built-in list then you can create your own custom format as follows:
Right-click on the cell where the date is.
Select Format Cells.
When the dialogue box opens select the Number tab.
In the Category [...]
