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 some times greater than 24 hours in Excel 2007

To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :

A1 = 02:30

A2 = 10:15

A3 has the formula: =SUM(A1:A2)

then A3 will have the correct values of 12:45.

However if your total times are greater than 24 hours then things get tricky.

If you have :

A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.

To retain the hours beyond 24, you have two alternatives:

1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)

2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.

In both cases the formula will remain as it is. Only the cell format differs.

See another example below:

More posts on time:

http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/

Convert time in decimals to a standard time

If you have a time in a decimal format, i.e. an integer and a fraction, e.g. 2.5 hours (2 and a half hours) and you want to convert to a standard time format 02:30 (2 hours and 30 minutes), then you can use a formula based on the TIME function with the following syntax:

TIME(hours,minutes,seconds)

Example:

We have a decimal time 2.5 in cell A2. we can use the following formula to convert it to a standard time:

=TIME(INT(A2);(A2-INT(A2))*60;0)

  • The first parameter in this formula,  hours is equal to the integer part of the decimal value: INT(A2).
  • The second parameter, minutes is equal to the fraction of the decimal value multiplied by 60:  (A2-INT(A2))*60.
  • The last parameter seconds, is set to 0 in our example.

Below are different examples based on this formula:

dtime1

Applies to Excel 2003

Add hours or minutes to a standard time

If you have a standard time, current time for example, and you want to add to it hours, minutes or seconds to get to a new time, then what you need to do is to convert the hours or minutes to a standard time format using the TIME function. After that you can add the two values together using the “+” sign.

A generic formula will look like this:

New time = current time + Time(hours,minutes,seconds)

In the examples below we have a standard time (07:15) in column A.

1. In row 2 we are adding 3 hours (B2) to 07:15 (A2). The formula in D2 will be:  =A2+TIME(B2;C2;0). The new time is 10:15.

2. In row 3 we are adding 4 hours (B3) and 15 minutes (C3) to 07:15 (A3). The formula in D3 will be:  =A3+TIME(B3;C3;0). The new time is 11:30.

3. In row 4 we are adding 20 minutes (C4) to 07:15 (A4). The formula in D4 will be:  =A4+TIME(B4;C4;0). The new time is 07:35.

4. In row 5 we are adding 12 hours (B5) to 07:15 (A5). The formula in D5 will be:  =A5+TIME(B5;C5;0). The new time is 19:15.

5. The last example in row six is slightly different. The result of the addition is more than 24 hours. If we use a formula similar to the ones shown above the result will be 01:15 while the actual value is 25 hours and 15 minutes. I.e. anything more than 24 hours will be lost. To avoid this problem we modify the formula in cell D6 slightly to look like this:

=(A6+TIME(B6;0;0))*24

We will also change the format of cell D6 to number instead of time. The result of this formula is 25.25 hours(.25 to the right of the decimal point is a decimal value and not minutes).

Notes:

1. Cells that contain time (Columns A and D should be formatted as TIME, cells that contain Hours or minutes (columns B an C) should be formatted as NUMBER).

2. I did not include seconds in these examples. If you want to add seconds then you can specify it as the third parameter of the TIME function.

3. The examples use a 24 hour clock. You can use 12 hour clock with AM/PM without problems.

addtime4

Applies to Excel 2003

How to add or sum times

If you have a number of time values you want to add together, e.g. total hours worked by an employee in the week, then you can list those hours in a standard time format (hh:mm or hh:mm:ss) and sum them together.

Example 1 : total hours less than 24.

Cells B1, B2, B3 and B4 contains the times.

Cell B5 (total) contains the formula:

=SUM(B1:B4)

The result of this sum is 23:05 (23 hours and 5 minutes).

Please note that cells B1to B5 are formatted as time (hh:mm).

addtime1

Example 2 – total hours greater than 24.

Cells B1, B2, B3 and B4 contains the times.

The total time in this example is 25 hours and 5 minutes. If we use the formula : =SUM(B1:B4), the result will be 01:05 (i.e 24 hours are gone).

To avoid this problem we use the following formula instead: =SUM(B1:B4)*24

We will also change the format of cell B5 to number instead of time. The result of this sum is 25.08 hours(.08 is a decimal value and not minutes).

addtime2

Applies to Excel 2003

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 calculate the number of days 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 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