You are here: Home / Archives for Month

How to extract day, month or year from a date

January 8, 2009 By

To extract day, month or year from a date, you use the corresponding Excel built in function for each one of these date parts.

  • Day: Day(mydate).
  • Month: Month(mydate).
  • Year: Year(mydate).

Examples:

Suppose you have in cell A1 (formatted as date), the date:15/11/2009 (ddmmyyyy), Then:

  • Day(A1) will result in 15
  • Month(A1) will result in 11
  • Year(A1) will result in 2009

Applies to: Excel 2003

Filed Under: Date and Time, Functions Tagged With: , , ,

Calculate the number of months between two dates

November 16, 2008 By

If you want the number of months occurring regardless of the day of the month then continue reading. If you want to calculate ‘whole month intervals’ by considering the day of the month, then check this post.

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 you have the date 25/09/2007 in cell A1 and the date 31/12/2007 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=MONTH(B1) – MONTH(A1)

This should give a result of 3.

Note that the cell where you write the formula must be formatted as number and not date.

Case 2 – dates occur in two different years

Use the the YEAR function and the MONTH function.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/09/2007 in cell A1 and the date 31/03/2008 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

This should give a result of 6.

Again the cell where you write the formula must be formatted as number and not date.

Filed Under: Date and Time, Formulas Tagged With: , ,

How to format a date with month in upper case

July 31, 2008 By

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 example then our functions will be as follows:

=UPPER(TEXT(A1,”mmm”))

If you want to return the full date then you have to concatenate the day and the year with the month as follows:

=DAY(A1)&UPPER(TEXT(A1,”mmm”))&YEAR(A1)

The final result will be somthing like: 15AUG2008

Filed Under: Date and Time, Formatting, Formulas, Functions Tagged With: , , ,