How to calculate age in Excel 2010

To calculate a person (or anything else) age you basically subtract his date of birth from today’s date. However which date functions to use depends on the amount of precision and the output format you want.

Suppose that we want to calculate the age of someone who is born on 15/06/1973 and we have his birth date in cell A1.

1. If we want his age in whole number years only, e.g. 40 years, then we can use this formula:

=YEAR(TODAY())-YEAR(A1)

2. If we want his age in whole numbers and a fraction of a year, e.g. 40.5 years, then we can use this formula:

=(TODAY()-A1)/365.25

3. If we want his age in years and months, then we can use a formula like this one:

=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”

The output of this formula will be as follows:

40 years and 2 months.

4. If we want to extend the previous formula to include days, then we can use a complex formula like this one:

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375;1)*30.4375) & ” days”

The output of this formula will be as follows:

40 years, 2 months and 18 days.

This formula could yield a one day difference due to approxmation.

What if you want to calculate the age in a target date other than today’s date?

Well, simply put the target date in another cell and replace the function TODAY() with the reference of the target cell. In the previous example if we want to calculate the age in 31 July 1995, then we can put 31/7/1995 in cell B1 and modify the first formula to be like this:

=YEAR(B1)-YEAR(A1)

Note:

Excel does not recognize dates before year 1900 (windows) or 1904(Macintosh). So formulas given above are also subject to Excel limitations.

 

 

 

How to insert current date in worksheet footer

To insert the current date in worksheet footer, follow these steps:

1. In the File menu click Page Setup. The Page Setup window will open.
2. Select the Header/Footer tab.
3. Click the Custom Footer button. The Footer window will open.

footicons

As you can notice the footer is divided into three sections; left, center and right. Above those sections there is a number of icons for page number, pages, date, time and more other icons.

To insert the current date:

1. Click on the section where you want the date to be inserted.
2. Click the Date icon footerdate.
3. The following code will be inserted in the selected footer section: “&[Date]”.
4. Click OK and you will see the current date in the preview window.
5. Click OK again to finish the dialog.

Notes:

1. You can also go to Header/Footer by clicking  Header and Footer in the View menu.
2. Use File –> Print Preview to see how your footer will look like in the printer.
3. You can use a similar procedure to insert the date in the header if you want to.

Applies to: Excel 2003

Calculate the difference between two times

The way you calculate the difference between two times depends on the way you want to present your results:

1. Present the result in standard time format (hh:mm:ss).

Simply subtract the two times from each other and format the result cell as time.

Examples:

A1 = 9:15:00
B1 = 14:07:20
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

or if you are using a 12 hour clock then the same example will be like this:

A1 = 9:15:00 AM
B1 = 02:07:20 PM
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

If you want the difference in hours and minutes or even in hours only, then just customize the cell format for that purpose.

Notes:

When you use this option then:

1. Your result should always be positive. I.e. the time in B1 must be later than the time in A1.

2. Hours will not exceed 24 and minutes  or seconds will not exceed 60.

For more help on time formatting check this post:

How to create a custom time format

2. Present the result as a total of hours, minutes or seconds:

Example:

A3 = 1/25/2009  21:03

B3 = 1/26/2009  23:17

1. To get the total hours between the two times use the formula:

=INT((B3-A3)*24). The result is 26.

2. To get the total minutes between the two times use the formula:

=(B3-A3)*1440. The result is 1574.

3. To get the total seconds between the two times use the formula:

=(B3-A3)*86400. The result is 94462.

Notes:

1. In all three cases (B3-A3) will give the number of days. Then you multiply by the apprpriate constant to get either hours, minutes or seconds.

2. INT function is used in the first case to get whole number. If you are interested in the fraction then you can ignore the INT function.

How to extract day, month or year from a date

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

Calculate the number of months between two dates – knowledgebase article

This step-by-step article shows you how to calculate the number of months between any two dates.

To calculate the number of months between any two dates, use one of the following methods. Note that both methods use the following information:

• EDate = Earlier Date
• LDate = Later Date

NOTE: If you type LDate or EDate directly into this formula instead of into their cell references, you must surround it by quotation marks (for example, “9/7/00”).

Round Up

This method does not use the day of the month in its calculations. For example, given a start date of 10/31/00 and an end date of 11/2/00, one month is returned even though only two days elapsed.

For this method, use the following formula:

=(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

as shown in the following example:

1. Type 10/31/99 in cell A1.
2. Type the following formula in cell A2:

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

The number that is returned equals the number of months from 10/31/99 to today’s date, rounded up to the next whole number.

Round Down

This method uses the number days in its calculations and rounds down to the nearest number of whole months. For example, given a start date of 10/30/00 and an end date of 11/30/00, one month is returned; however, an end date of 11/29/00 returns a zero.

For this method, use the following formula

=IF(DAY(LDate)>=DAY(EDate),0,-1)+YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)

as shown in the following example:

1. Type 10/31/99 in cell A1.
2. Type the following formula in cell B1:

=IF(DAY(NOW())>=DAY(A1),0,-1)+(YEAR(NOW())-YEAR(A1)) *12+MONTH(NOW())-MONTH(A1)

NOTE: Ensure that cell B1 is formatted as General. The number returned equals the number of months from 10/31/99 to today’s date, rounded down to the nearest number of whole months.

Source: Microsoft Help and Support
Original article: http://support.microsoft.com/kb/214134

How to get the current date and time

If you want to get the current date and time in a cell then you can do one of the following:

1. To get the current date only use the TODAY() function.

Syntax

=TODAY()

2. To get the current date and time use the NOW() function.

Syntax

=NOW()

3. To get the current time only use the NOW() function, and then format the cell to display time only.

Note:

The TODAY() and NOW() functions will update the date and time whenever the worksheet is calculated. I.e. their values are variable.

To insert static date and/or time in a cell:

1. For current date: Select a cell, press CTRL then enter “;” (semicolon character).

2. For current time: Select a cell, press CTRL+SHIFT then enter “;”.

3. For current date and time: Select a cell, press CTRL then enter “;” to get the date. Enter SPACE, press CTRL+SHIFT then enter “;” to get the time.

You can also use Copy/Paste special. For example to To insert static date and time in a cell:

1. Enter “=NOW()” in a cell and hit enter. You will get the current date and time

2. Right click on the cell and select Copy.

3. Right click on the same cell and select Paste Special, then select Paste Values.

How to insert update date or timestamp in a cell

A few months ago a user asked this question in Excel Help Forum:

If you have two columns, is it possible to have column B put in the date that data was added to column A?

I have replied to the question in the forum at that time, and I have noticed since then that other users are asking the same question. For that reason I thought it might be a good idea to elaborate on the subject here.

To insert an update date or a timestamp in a cell, you need to use a worksheet event called “Worksheet Change”. Just follow these steps:

1. Switch to visual basic editor by clicking Tools –> Macro –> visual basic editor.

2. Click on the name of your sheet in project explorer at your left.

Timestamp1

3. On the right pane select “Worksheet” from the left drop down and select “Change” from the right drop down.

4. Now you are in the Worksheet_Change event.

5. Copy this code between “Private Sub Worksheet_Change” and “End Sub”.

Col = Left(Target.Address, 2)
If Col = “$A” Then Target.Offset(0, 1) = Now

Timestamp2

Now whenever you change a cell in column “A” the next cell in column “B” will be updated with date and time.

You can change the code to suit your own needs, but the main idea is that you have to use the “Worksheet Change” event for this purpose.

Calculate the number of months between two dates

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.

How to calculate the difference between two dates

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 – A1

This should give a result of 51 ( 5 days in Nov + 31 in Dec + 15 in Jan).

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

How to format a date with month in upper case

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