Excel Tutorial: DateTime Calculation (calculate difference, age, add/subtract)
In Excel, the date time calculation is used frequently, such as calculating the difference between two dates/times, adding or subtracting datetime, getting age based on the give birth date and so on. Here in this tutorial, it lists almost scenarios about datetime calculation and provides the related methods for you.
In this tutorial, I create some examples to explain the methods, you can change the references for you need when you use below VBA code or formulas
1. Calculate difference between two dates/times
Calculating difference between two dates or two times may be the most normal problem of datetime calculation you meet in daily Excel work. Follower below examples provided may help you to enhance the efficiency when you encounter the same problems.
1.11 Calculate difference between two dates in days/months/years/weeks
Excel DATEDIF function can be used to quickly calculate the difference between two dates in days, months, years, and weeks.
Click for more details about DATEDIF function
Days difference between two dates
To get the days difference between two dates in cell A2 and B2, please use the formula as this
=DATEDIF(A2,B2,"d")
Press Enter key to get the result.
Months difference between two dates
To get the months difference between two dates in cell A5 and B5, please use the formula as this
=DATEDIF(A5,B5,"m")
Press Enter key to get the result.
Years difference between two dates
To get the years difference between two dates in cell A8 and B8, please use the formula as this
=DATEDIF(A8,B8,"y")
Press Enter key to get the result.
Weeks difference between two dates
To get the weeks difference between two dates in cell A11 and B11, please use the formula as this
=DATEDIF(A11,B11,"d")/7
Press Enter key to get the result.
Note:
1) When you use the formula above to get the weeks difference, it may return a result in date format, you need to format the result to general or number as you need.
2) When you use the formula above to get the weeks difference, it may return to a decimal number, if you want to get the integer week number, you can add ROUNDDOWN function before as below shown to get the integer weeks difference:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Calculate months ignore years and days between two dates
If you just want to calculate the months difference ignoring years and days between two dates as the below screenshot shows, here is a formula that can help you.
=DATEDIF(A2,B2,"ym")
Press Enter key to get the result.
A2 is the start date, and B2 is the end date.
1.13 Calculate days ignore years and months between two dates
If you just want to calculate the days difference ignoring years and months between two dates as below screenshot shown, here is a formula that can help you.
=DATEDIF(A5,B5,"md")
Press Enter key to get the result.
A5 is the start date, and B5 is the end date.
1.14 Calculate difference between two dates and return years, months and days
If you want to get the difference between two dates and return xx years, xx months, and xx days as the below screenshot shows, here is also a formula provided.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Press Enter key to get the result.
A8 is the start date, and B8 is the end date.
1.15 Calculate difference between a date and today
To automatically calculate the difference between a date and today, just change the end_date in the above formulas to TODAY(). Here take to calculate days difference between a past date and today as an instance.
=DATEDIF(A11,TODAY(),"d")
Press Enter key to get the result.
Note: if you want to calculate the difference between a future date and today, change the start_date to today, and take the future date as end_date like this:
=DATEDIF(TODAY(),A14,"d")
Notice that the start_date must be smaller than end_date in the DATEDIF function, otherwise, it will return to #NUM! error value.
1.16 Calculate workdays with or without holiday between two dates
Sometimes, you may need to count the number of workdays with or without the holidays between two given dates.
In this part, you will use the NETWORKDAYS.INTL function:
Click NETWORKDAYS.INTL to know its arguments and usage.
Count workdays with holidays
To count the workdays with holidays between two dates in cell A2 and B2, please use the formula as this:
=NETWORKDAYS.INTL(A2,B2)
Press Enter key to get the result.
Count workdays without holidays
To count the workdays with holidays between two dates in cell A2 and B2, and excluding the holidays in range D5:D9, please use the formula as this:
=NETWORKDAYS.INTL(A5,B5,1,D5:D9)
Press Enter key to get the result.
Note:
In the above formulas, they take Saturday and Sunday as weekend, if you have different weekend days, please change the argument [weekend] as you need.
1.17 Calculate weekends between two dates
If you want to count the number of weekends between two dates, the SUMPRODUCT or SUM functions can do you a favor.
To count the weekends (Saturday and Sunday) between two dates in cell A12 and B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Press Enter key to get the result.
1.18 Calculate specific weekday between two dates
To count the number of a specific weekday such as Monday between two dates, the combination of INT and WEEKDAY functions can help you.
Cell A15 and B15 are the two dates that you want to count Monday between, please use formula as this:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Press Enter key to get the result.
Change the number of the weekday in WEEKDAY function to count a different weekday:
1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday and 7 is Saturday)
1.19 Calculate remaining days in month/year
Sometimes, you may want to know the remaining days in the month or year based on the supplied date as the below screenshot shows:
Get remaining days in current month
Click EOMONTH to know the argument and usage.
To get the remaining days of the current month in cell A2, please use the formula as this:
=EOMONTH(A2,0)-A2
Press Enter key, and drag auto fill handle to apply this formula to other cells if needed.
Tip: the results may be shown as date format, just change them as general or number format.
Get remaining days in current year
To get the remaining days of the current year in cell A2, please use the formula as this:
=DATE(YEAR(A2),12,31)-A2
Press Enter key, and drag auto fill handle to apply this formula to other cells if needed.
1.21 Calculate difference between two times
To get the difference between two times, here are two simple formulas that can help you.
Supposing in cell A2 and B2 contain start_time and end_time separately, using the formulas as these:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Press Enter key to get the result.
Note:
- If you use end_time-start_time, you can format the result to another time format as you need in the Format Cells dialog.
- If you use TEXT(end_time-first_time,"time_format"), enter the time format you want the result shown in the formula, such as TEXT(end_time-first_time,"h") returns 16.
- If the end_time is smaller than start_time, both formulas return error values. For solving this problem, you can add ABS in the front of these formulas, such as ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), then format the result as time.
1.22 Calculate difference between two times in hours/minutes/seconds
If you want to calculate the difference between two times in hours, minutes, or seconds as the below screenshot shows, please follow this part.
Get hours difference between two times
To get the hours difference between two times in A5 and B5, please use the formula as this:
=INT((B5-A5)*24)
Press Enter key, then format the time format result as general or number.
If you want to get the decimal hours difference, use (end_time-start_time)*24.
Get minutes difference between two times
To get the minutes difference between two times in A8 and B8, please use the formula as this:
=INT((B8-A8)*1440)
Press Enter key, then format the time format result as general or number.
If you want to get the decimal minutes difference, use (end_time-start_time)*1440.
Get seconds difference between two times
To get the seconds difference between two times in A5 and B5, please use the formula as this:
=(B11-A11)*86400)
Press Enter key, then format the time format result as general or number.
1.23 Calculate hours difference only between two times (not exceed 24 hours)
If the difference between two times does not exceed 24 hours, the HOUR function can quickly get the hours difference between these two times.
Click HOUR for more details about this function.
To get the hours difference between times in cell A14 and B14, please use HOUR function as this:
=HOUR(B14-A14)
Press Enter key to get the result.
The start_time must be smaller than end_time, otherwise, the formula returns #NUM! error value.
1.24 Calculate minutes difference only between two times (not exceed 60 minutes)
The MINUTE function can quickly get the only minutes difference between these two times and ignore hours and seconds.
Click MINUTE for more details about this function.
To get only the minutes difference between times in cell A17 and B17, please use MINUTE function as this:
=MINUTE(B17-A17)
Press Enter key to get the result.
The start_time must be smaller than end_time, otherwise, the formula returns #NUM! error value.
1.25 Calculate seconds difference only between two times (not exceed 60 seconds)
The SECOND function can quickly get the only seconds difference between these two times and ignore hours and minutes.
Click SECOND for more details about this function.
To get only the seconds difference between times in cell A20 and B20, please use SECOND function as this:
=SECOND(B20-A20)
Press Enter key to get the result.
The start_time must be smaller than end_time, otherwise, the formula returns #NUM! error value.
1.26 Calculate difference between two times and return hours, minutes, seconds
If you want to show the difference between two times as xx hours xx minutes xx seconds, please use TEXT function as below shown:
Click TEXT to realize the arguments and usage of this function.
To calculate the difference between times in cell A23 and B23, use the formula as this:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Press Enter key to get the result.
Note:
This formula also only calculates the hours difference not exceeding 24 hours, and the end_time must be greater than start_time, otherwise, it returns #VALUE! error value.
1.27 Calculate difference between two datetimes
If there are two times in format mm/dd/yyyy hh:mm:ss, to calculate the difference between them, you can use one of below formulas as you need.
Get time difference between two datetimes and return result in hh:mm:ss format
Take two datetimes in cell A2 and B2 as an instance, please use the formula as this:
=B2-A2
Press Enter key, returning a result in datetime format, then format this result as [h]:mm:ss in the custom category under the Number tab in Format Cells dialog.
Get difference between two datetimes and return days, hours, minutes, seconds
Take two datetimes in cell A5 and B5 as an instance, please use the formula as this:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Press Enter key to get the result.
Note: in both formulas, end_datetime must be greater than start_datetime, otherwise, the formulas return error values.
1.28 Calculate time difference with milliseconds
Firstly, you need to know how to format the cell to show the milliseconds:
Select the cells that you want to show milliseconds and right to select Format Cells to enable the Format Cells dialog, select Custom in the Category list under Number tab, and type this hh:mm:ss.000 into the text box.
Use formula:
Here to calculate the difference between two times in cell A8 and B8, use the formula as:
=ABS(B8-A8)
Press Enter key to get the result.
1.29 Calculate working hours between two dates excluding weekends
Sometimes, you may need to count the working hours between two dates, excluding weekends (Saturday and Sunday).
Here the working hours are fixed at 8 hours every day, and to calculate the working hours between two dates provided in cell A16 and B16, please use the formula as this:
=NETWORKDAYS(A16,B16) * 8
Press Enter key and then format the result as general or number.
For more examples about calculating working hours between two dates, please visit Get Work Hours Between Two Dates In Excel
If you have Kutools for Excel installed in Excel, 90 percents of datetime difference calculations can be quickly solved without remembering any formulas.
1.31 Calculate difference between two datetimes by Data & Time Helper
To calculate the difference between two datetimes in Excel, just the Date & Time Helper is enough.
1. Select a cell where you place the calculated result, and click Kutools > Formula Helper > Date & Time helper.
2. In the popping Date & Time Helper dialog, follow the below settings:
- Check Difference option;
- Select the start datetime and end datetime in Arguments input section, you also can directly enter datetime manually into the input box, or click the calendar icon to select the date;
- Select the output result type from the drop-down list;
- Preview the result in Result section.
3. Click Ok. The calculated result is outputted, and drag the autofill handle over the cells that you also need to calculate.
Tip:
If you want to get the difference between two datetime and show the result as days, hours, and minutes with Kutools for Excel, please do as below:
Select a cell where you want to place the result, and click Kutools > Formula Helper > Date & Time > Count days, hours and minutes between two dates.
Then in the Formulas Helper dialog, specify the start date and end date, then click Ok.
And the difference result will be shown as days, hours, and minutes.
Click Date & Time Helper to know more usage of this feature.
Click Kutools for Excel to know all features of this add-in.
Click Free Download to get 30-day free trial of Kutools for Excel
1.32 Calculate weekend/workday/specific weekday difference between two datetimes by Formula Helper
If you want to quickly count the weekend, workdays or a specific weekday between two datetimes, Kutools for Excel’s Formula Helper group can help you.
1. Select the cell that will place the calculated result, click Kutools > Statistical > Number of non-working days between two dates/Number of working days between two dates/Count the number of specific day of the Week.
2. In the popping out Formulas Helper dialog, specify the start date and end date, if you apply Count the number of specific day of the week, you need to specify the weekday too.
To count the specific weekday, you can refer to the note to use 1-7 to indicate Sunday-Saturday.
3. Click Ok, and then drag the autofill handle over cells that need to count number of weekend/workday/specific weekday if need.
Click Kutools for Excel to know all features of this add-in.
Click Free Download to get 30-day free trial of Kutools for Excel
2. Add or subtract date and time
Except for calculating the difference between two datetimes, adding or subtracting is also the normal datetime calculation in Excel. For example, you may want to get the due date based on the producing date and the number of preservation days for a product.
2.11 Add or subtract days to a date
To add or subtract a specific number of days to a date, here are two different methods.
Supposing to add 21 days to a date in cell A2, please choose one of the below methods to solve it,
Method 1 date+days
Select a cell and type the formula:
=A+21
Press Enter key to get the result.
If you want to subtract 21 days, juts change plus sign (+) to minus sign (-).
Method 2 Paste Special
1. Type the number of days you want to add in a cell supposing in cell C2, and then press Ctrl + C to copy it.
2. Then select the dates you want to add 21 days, right-click to show the context menu, and select Paste Special....
3. In the Paste Special dialog, check Add option (If you want to subtract days, check Subtract option). Click OK.
4. Now the original dates change to 5-digit numbers, format them as dates.
2.12 Add or subtract months to a date
To add or subtract months to a date, EDATE function can be used.
Click EDATE to study its arguments and usage.
Supposing to add 6 months to the date in cell A2, use formula as this:
=EDATE(A2,6)
Press Enter key to get the result.
If you want to subtract 6 months to the date, change 6 to -6.
2.13 Add or subtract years to a date
To add or subtract n years to a date, a formula that combines DATE, YEAR, MONTH and DAY functions can be used.
Supposing to add 3 years to the date in cell A2, use the formula as this:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Press Enter key to get the result.
If you want to subtract 3 years to the date, change 3 to -3.
2.14 Add or subtract weeks to a date
To add or subtract weeks to a date, the general formula is
Supposing to add 4 weeks to the date in cell A2, use the formula as this:
=A2+4*7
Press Enter key to get the result.
If you want to subtract 4 weeks to the date, change plus sign (+) to minus sign (-).
2.15 Add or subtract workdays including or excluding holidays
In this section, it introduces how to use WORKDAY function to add or subtract working days to a given date excluding holidays or including holidays.
Visit WORKDAY to know more details about its arguments and usage.
Add workdays including holidays
In cell A2 is the date that you use, in cell B2 contains the number of days that you want to add, please use the formula as this:
=WORKDAY(A2,B2)
Press Enter key to get the result.
Add workdays excluding holidays
In cell A5 is the date that you use, in cell B5 contains the number of days that you want to add, in range D5:D8 lists the holidays, please use the formula as this:
=WORKDAY(A5,B5,D5:D8)
Press Enter key to get the result.
Note:
WORKDAY function takes Saturday and Sunday as weekends, if your weekends are on Saturday and Sunday, you can apply WOKRDAY.INTL function, which supports specifying weekends.
Visit WORKDAY.INTL for more details.
If you want to subtract workdays to a date, just change the number of days to negative in the formula.
2.16 Add or subtract specific year, month, days to a date
If you want to add a specific year, month days to a date, the formula which combines DATE, YEAR, MONTH, and DAYS function can do you a favor.
To add 1 year 2 months and 30 days to a date in A11, please use the formula as this:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Press Enter key to get the result.
If you want to subtract, change all plus signs (+) to minus signs (-).
2.21 Add or subtract hours/minutes/seconds to a datetime
Here provides some formulas for adding or subtracting hours, minutes, or seconds to a datetime.
Add or subtract hours to a datetime
Supposing to add 3 hours to a datetime (also can be a time) in cell A2, please use the formula as this:
=A2+3/24
Press Enter key to get the result.
Add or subtract hours to a datetime
Supposing to add 15 minutes to a datetime (also can be a time) in cell A5, please use the formula as this:
=A2+15/1440
Press Enter key to get the result.
Add or subtract hours to a datetime
Supposing to add 20 seconds to a datetime (also can be a time) in cell A8, please use the formula as this:
=A2+20/86400
Press Enter key to get the result.
Supposing there is a table of Excel recording the working time of all staffs in a week, to sum the total working time for calculating the payments, you may use SUM(range) to get the result. But in general, the summed result will be shown as a time not exceeding 24 hours as the below screenshot shows, how can you get the correct result?
Actually, you just need to format the result as [hh]:mm:ss.
Right-click on the result cell, choose Format Cells in the context menu, and in the popping Format Cells dialog, choose Custom from the blist, and type [hh]:mm:ss into the textbox in right section, click OK.
The summed result will be shown correctly.
2.23 Add working hours to a date excluding weekend and holiday
Here provides a long formula for getting the end date based on adding a specific number of working hours to a start date and excludes weekends (Saturday and Sunday) and holidays.
In an Excel table, A11 contains the start datetime, and B11 contains the working hours, in cell E11 and E13 are the working start and end times, and cell E15 contains the holiday that will be excluded.
Please use the formula as this:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Press Enter key to get the result.
If you have Kutools for Excel installed, only one tool – Date & Time Helper can solve most of the calculations on adding and subtracting datetime.
1. Click a cell that you want to output the result, and apply this tool by clicking Kutools > Formula Helper > Date & Time Helper.
2. In the Date & Time Helper dialog, check Add option or Subtract option as you need, then choose the cell or directly type the datetime you want to use in Arguments input section, then specify the years, months, weeks, days, hours, minutes and seconds that you want to add or subtract, then click Ok. See screenshot:
You can preview the calculated result in the Result section.
Now the result is outputted, drag auto handle over other cells to get the results.
Click Date & Time Helper to know more usage of this feature.
Click Kutools for Excel to know all features of this add-in.
Click Fee Download to get 30-day free trial of Kutools for Excel
2.41 Check or highlight if a date is expired
If there is a list of expired dates of products, you may want to check and highlight the dates which are expired based on today as the below screenshot shows.
Actually, the Conditional Formatting can quickly handle this job.
1. Select the dates that you want to check, then click Home > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog, select Use a formula to determine which cells to format in the Select a Rule Type section, and type =B2<TODAY() into the input box (B2 is the first date you want to check), and click Format to pop up Format Cells dialog, then choose a different formatting to outstand the expired dates as you need. Click OK > OK.
2.42 Return the end of current month/first day of next month/a>
The expired dates of some products are at the end of the producing month or the first day of the next month of production, for quickly list the expired dates based on the producing date, please follow this part.
Get end of the current month
Here is a producing date in cell B13, please use the formula as this:
=EOMONTH(B13,0)
Press Enter key to get the result.
Get 1st day of next month
Here is a producing date in cell B18, please use the formula as this:
=EOMONTH(B18,0)+1
Press Enter key to get the result.
3. Calculate age
In this section, it lists the methods on solving how to calculate age based on a given date or a series number.
3.11 Calculate age based on given birthdate
Get age in decimal number based on birthdate
Click YEARFRAC for details about its arguments and the usage.
For instance, to get the ages based on the list of birthdates in column B2:B9, please use the formula as this:
=YEARFRAC(B2,TODAY())
Press Enter key, then drag the autofill handle down until all ages are calculated.
Tip:
1) You can specify the decimal place as you need in the Format Cells dialog.
2) If you want to calculate the age on a specific date based on a given birthdate, change TODAY() to the specific date enclosed with double quotations such as =YEARFRAC(B2,"1/1/2021")
3) If you want to get the next year’s age based on the birthdate, just add 1 in the formula such as =YEARFRAC(B2,TODAY())+1.
Get age in whole number based on birthdate
Click DATEDIF for details about its arguments and the usage.
Using the above example, to get the age based on the birthdates in the list in B2:B9, please use the formula as this:
=DATEDIF(B2,TODAY(),"y")
Press Enter key, then drag the auto-fill handle down until all ages are calculated.
Tip:
1) If you want to calculate the age on a specific date based on a given birthdate, change TODAY() to the specific date enclosed with double quotations such as =DATEDIF(B2,"1/1/2021","y").
2) If you want to get the next year’s age based on the birthdate, just add 1 in the formula such as =DATEDIF(B2,TODAY(),"y")+1.
3.12 Calculate age in years, month and days format by given birthday
If you want to calculate age based on a given birthdate, and show the result as xx years, xx months, xx days as the below screenshot shows, here is a long formula that can help you.
To get the age in years, months, and days based on the birthdate in cell B12, please use the formula as this:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Press Enter key to get the age, then drag the autofill handle down to other cells.
Tip:
If you want to calculate the age in a specific date based on a given birthdate, change TODAY() to the specific date enclosed with double quotations such as = =DATEDIF(B12,"1/1/2021","Y")&" Years, "&DATEDIF(B12,"1/1/2021","YM")&" Months, "&DATEDIF(B12,"1/1/2021","MD")&" Days".
3.13 Calculate age by birth of date before 1/1/1900
In Excel, the date before 1/1/1900 cannot be entered as a datetime or calculated correctly. But if you want to calculate the age of a famous person based on the given birthdate (before 1/11900) and death date, only a VBA code can help you.
1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window, and click Insert tab and choose Module to create a new module.
2. Then copy and paste the below code to the new module.
VBA: Calculate age before 1/1/1900
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Save the code, and go back to the sheet and select a cell to place the calculated age, type =AgeFunc(birthdate,deathdate), in this case, =AgeFunc(B22,C22), press Enter key to get the age. And use auto fill handle to apply this formula to other cells if needed.
If you have Kutools for Excel installed in Excel, you can apply the Date & Time Helper tool to calculate the age.
1. Select a cell that you want to place the calculated age, and click Kutools > Formula Helper > Date & Time helper.
2. In the Date & Time Helper dialog,
- 1) Check Age option;
- 2) Choose the birthdate cell or directly enter the birthdate or click calendar icon to select the birthdate;
- 3) Choose Today option if you want to calculate the current age, choose Specified date option and enter the date if you want to calculate the age in the past or future;
- 4) Specify the output type from the drop-down list;
- 5) Preview the output result. Click Ok.
Click Date & Time Helper to know more usage of this feature.
Click Kutools for Excel to know all features of this add-in.
Click Free Download to get 30-day free trial of Kutools for Excel
3.31 Get birthday from ID number
If there is a list of ID numbers which use the first 6 digits to record the birthdate such as 920315330 means that the birthdate is 03/15/1992, how can you quickly get the birthdate into another column?
Now let’s take the list of ID numbers starting in cell C2 as an instance, and use the formula as this:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Press Enter key. Then drag the autofill handle down to get other results.
Note:
In the formula, you can change the reference to your need. For example, if the ID number shown as 13219920420392, the birthday is 04/20/1992, you can change the formula to =MID(C2,8,2)&"/"&MID(C2,10,2)&"/"&MID(C2,4,4) to get the correct result.
3.32 Calculate age from ID number
If there is a list of ID numbers which use the first 6 digits to record the birthdate such as 920315330 means that the birthdate is 03/15/1992, how can you quickly calculate the age based on each ID number in Excel?
Now let’s take the list of ID numbers starting in cell C2 as an instance, and use the formula as this:
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")
Press Enter key. Then drag the autofill handle down to get other results.
Note:
In this formula, if the year is less than the current year, the year will be considered as starting with 20, such as 200203943 will be considered as the year 2020; if the year is greater than the current year, the year will be considered as starting with 19, such as 920420392 will be considered as the year 1992.
More Excel Tutorials:
Combine Multiple Workbooks/Worksheets Into One
This tutorial, listing almost all combining scenarios you may face and providing relative professional solutions for you.
Split Text, Number, And Date Cells (Separate Into Multiple Columns)
This tutorial is divided into three parts: split text cells, split number cells and split date cells. Each part provides different examples to help you know how to handle the splitting job when encountering the same problem..
Combine Contents Of Multiple Cells Without Losing Data In Excel
This tutorial narrows down the extraction to a specific position in a cell and collects different methods to help extract text or numbers from a cell by specific position in Excel.
Compare Two Columns For Matches And Differences In Excel
Here this article covers most possible scenarios of the comparing two columns you might meet, and hope it can help you.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
