Note: The other languages of the website are Google-translated. Back to English
English English

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.

The Navigations of This Tutorial

1. Calculate difference between two dates/times

1.1 Calculate day/month/year difference between two dates

1.11 Calculate difference between two dates in days/months/years/weeks

1.12 Calculate months ignore years and days between two dates

1.13 Calculate days ignore years and months between two dates

1.14 Calculate difference between two dates and return years, months and days

1.15 Calculate difference between a date and today

1.16 Calculate workdays with or without holiday between two dates

1.17 Calculate weekends between two dates

1.18 Calculate specific weekday between two dates

1.19 Calculate remaining days in month/year

1.2 Calculate difference between two timess

1.21 Calculate difference between two times

1.22 Calculate difference between two times in hours/minutes/seconds

1.23 Calculate hours difference only between two times (not exceed 24 hours)

1.24 Calculate minutes difference only between two times (not exceed 60 minutes)

1.25 Calculate seconds difference only between two times (not exceed 60 seconds)

1.26 Calculate difference between two times and return hours, minutes, seconds

1.27 Calculate difference between two datetimes

1.28 Calculate time difference with milliseconds

1.29 Calculate working hours between two dates excluding weekends

1.3 Calculate difference between two datetime with Kutools for Excel

1.31 Calculate difference between two datetimes by Data & Time Helper

1.32 Calculate weekend/workday/specific weekday difference between two datetimes by Formula Helper

1.4 Combine two columns if blank

1.41 Using IF function

1.42 Using VBA

2. Add or subtract date and time

2.1 Add or subtract days/months/years/weeks/workdays to a date

2.11 Add or subtract days to a date

2.12 Add or subtract months to a date

2.13 Add or subtract years to a date

2.14 Add or subtract weeks to a date

2.15 Add or subtract workdays including or excluding holidays

2.16 Add or subtract specific year, month, days to a date

2.2 Add or subtract hours/minutes/seconds to times

2.21 Add or subtract hours/minutes/seconds to a datetime

2.22 Sum times over 24 hours

2.23 Add working hours to a date excluding weekend and holiday

2.3 Add or subtract date/time by Kutools for Excel

2.4 Extension

2.41 Check or highlight if a date is expired

2.42 Return the end of current month/first day of next month

3. Calculate age

3.1 Calculate age based on date

3.11 Calculate age based on given birthdate

3.12 Calculate age in years, month and days format by given birthday

3.13 Calculate age by birth of date before 1/1/1900

3.2 Calculate age by birth by using Kutools for Excel

3.3 Calculate age or get birthdate based on a series number

3.31 Get birthday from ID number

3.32 Calculate age from ID number

 

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.1 Calculate day/month/year difference between two dates

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.
doc calculate difference between two dates 1

Click for more details about DATEDIF function

Days difference between two dates

DATEDIF(start_date,end_date,"d")

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.
doc calculate difference between two dates 1

Months difference between two dates

DDATEDIF(start_date,end_date,"m")

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.
doc calculate difference between two dates 1

Years difference between two dates

DDATEDIF(start_date,end_date,"y")

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.
doc calculate difference between two dates 1

Weeks difference between two dates

DDATEDIF(start_date,end_date,"d")/7

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

=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")
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

In this part, you will use the NETWORKDAYS.INTL function:

NETWORKDAYS.INTL(start_date,end_date,[weekend],[holiday])

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.

SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)>5))
SUM(INT((WEEKDAY(start_date-{1,7})+end_date-start+date)/7))

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.
doc calculate difference between two dates 1

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.

INT((WEEKDAY(start_date- weekday)-start_date +end_date)/7)

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.
doc calculate difference between two dates 1

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:
doc calculate difference between two dates 1

Get remaining days in current month

EOMONTH(date,0)-date

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.
doc calculate difference between two dates 1

Tip: the results may be shown as date format, just change them as general or number format.

Get remaining days in current year

DATE(YEAR(date),12,31)-date

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.
doc calculate difference between two dates 1


1.2 Calculate difference between two times

1.21 Calculate difference between two times

To get the difference between two times, here are two simple formulas that can help you.

end_time-start_time
TEXT(end_time-first_time,"time_format")

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

Get hours difference between two times

INT((end_time-start_time)*24)

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.
doc calculate difference between two dates 1

If you want to get the decimal hours difference, use (end_time-start_time)*24.

Get minutes difference between two times

INT((end_time-start_time)*1440)

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.
doc calculate difference between two dates 1

If you want to get the decimal minutes difference, use (end_time-start_time)*1440.

Get seconds difference between two times

(end_time-start_time)*86400

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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:

TEXT(end_time-start_time,"h"" hours ""m"" minutes ""s"" seconds""")

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1 doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

Use formula:

ABS(end_time-start_time)

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.
doc calculate difference between two dates 1

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).

NETWORKDAYS(start_date,end_date) * working_hours

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.
doc calculate difference between two dates 1

For more examples about calculating working hours between two dates, please visit Get Work Hours Between Two Dates In Excel


1.3 Calculate difference between two datetime with Kutools for 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.
doc calculate difference between two dates 1

1. Select a cell where you place the calculated result, and click Kutools > Formula Helper > Date & Time helper.
doc calculate difference between two dates 1

2. In the popping Date & Time Helper dialog, follow the below settings:

  1. Check Difference option;
  2. 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;
  3. Select the output result type from the drop-down list;
  4. Preview the result in Result section.

doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

Then in the Formulas Helper dialog, specify the start date and end date, then click Ok.
doc calculate difference between two dates 1

And the difference result will be shown as days, hours, and minutes.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1
doc calculate difference between two dates 1

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.1 Add or subtract days/months/years/weeks/workdays to a date

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

2. Then select the dates you want to add 21 days, right-click to show the context menu, and select Paste Special....
doc calculate difference between two dates 1

3. In the Paste Special dialog, check Add option (If you want to subtract days, check Subtract option). Click OK.
doc calculate difference between two dates 1

4. Now the original dates change to 5-digit numbers, format them as dates.
doc calculate difference between two dates 1

2.12 Add or subtract months to a date

To add or subtract months to a date, EDATE function can be used.

EDATE(date, months)

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.
doc calculate difference between two dates 1

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.

DATE(YEAR(date) + years, MONTH(date),DAY(date))

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.
doc calculate difference between two dates 1

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

date+weeks*7

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.
doc calculate difference between two dates 1

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.

WORKDAY(date,days,[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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1
doc calculate difference between two dates 1

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.

DATE(YEAR(date) + years, MONTH(date) + months, DAY(date) + days)

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.
doc calculate difference between two dates 1

If you want to subtract, change all plus signs (+) to minus signs (-).


2.2 Add or subtract hours/minutes/seconds to time

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.
doc calculate difference between two dates 1

Add or subtract hours to a datetime

Datetime+hours/24

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.
doc calculate difference between two dates 1

Add or subtract hours to a datetime

Datetime+minutes/1440

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.
doc calculate difference between two dates 1

Add or subtract hours to a datetime

Datetime+seconds/86400

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.
doc calculate difference between two dates 1

2.22 Sum times over 24 hours

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?
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1  doc calculate difference between two dates 1

The summed result will be shown correctly.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1


2.3 Add or subtract date/time by Kutools for Excel

If you have Kutools for Excel installed, only one tool – Date & Time Helper can solve most of the calculations on adding and subtracting datetime.
doc calculate difference between two dates 1

1. Click a cell that you want to output the result, and apply this tool by clicking Kutools > Formula Helper > Date & Time Helper.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

Now the result is outputted, drag auto handle over other cells to get the results.
doc calculate difference between two dates 1

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.4 Extension

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1  doc calculate difference between two dates 1

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

EOMONTH(date,0)

Here is a producing date in cell B13, please use the formula as this:

=EOMONTH(B13,0)

Press Enter key to get the result.
doc calculate difference between two dates 1

Get 1st day of next month

EOMONTH(date,0)+1

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.
doc calculate difference between two dates 1


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.1 Calculate age based on date

3.11 Calculate age based on given birthdate

doc calculate difference between two dates 1

Get age in decimal number based on birthdate

YEARFRAC(birthdate, TODAY())

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.
doc calculate difference between two dates 1

Tip:

1) You can specify the decimal place as you need in the Format Cells dialog.
doc calculate difference between two dates 1

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

DATEDIF(birthdate,TODAY(),”y”)

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

=DATEDIF(birthdate,TODAY(),"Y")&" Years, "&DATEDIF(birthdate,TODAY(),"YM")&" Months, "&DATEDIF(birthdate,TODAY(),"MD")&" Days"

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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

doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

3.2 Calculate age by birth by using Kutools for Excel

If you have Kutools for Excel installed in Excel, you can apply the Date & Time Helper tool to calculate the age.
doc calculate difference between two dates 1

1. Select a cell that you want to place the calculated age, and click Kutools > Formula Helper > Date & Time helper.
doc calculate difference between two dates 1

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.

doc calculate difference between two dates 1

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.3 Calculate age or get birthdate based on a series number

doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.
doc calculate difference between two dates 1

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.



  • 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...
kte tab 201905
  • 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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations