*In this tutorial, you will find a simple explanation of the Excel DATEDIF function and a few formula examples that demonstrate how to compare dates and calculate the difference in days, weeks, months or years.*

Over the past few weeks, we investigated nearly every aspect of working with dates and times in Excel. If you have been following our blog series, you already know how to insert and format dates in your worksheets, how to calculate days, weeks, months and years as well as add and subtract dates.

In this tutorial, we will focus on calculating date difference in Excel and you will learn different ways to count the number of days, weeks, months and years between two dates.

As its name suggests, the DATEDIF function is purposed for calculating the difference between two dates.

DATEDIF is one of very few undocumented functions in Excel, and because it is "hidden" you won't find it on the *Formula* tab, nor will you get any hint on which arguments to enter when you start typing the function's name in the formula bar. That is why it's important to know the complete syntax of Excel DATEDIF to be able to use it in your formulas.

The syntax of the Excel DATEDIF function is as follows:

DATEDIF(start_date, end_date, unit)

All three arguments are required:

**Start_date** - the initial date of the period you want to calculate.

**End_date** - the ending date of the period.

To put it differently, start_date and end_date are two dates to calculate the difference between. In your DATEDIF formulas, you can input the dates is various ways, such as:

- Cell references. For example, the following formula counts the number of days between the dates in cells A1 and B1:
`=DATEDIF(A1, B1, "d")`

- Text strings. Excel understands dates in many formats, for example "20-May-2015", "5/20/2015", "2015/5/20", etc. The following formula calculates the number of months between the specified dates:
`=DATEDIF("5/10/2015", "7/20/2015", "m")`

- Serial numbers. Since Microsoft Excel stores each date as a serial number beginning with January 1, 1900, you can put numbers corresponding to the dates directly in the formula. Although fully supported, this method is not reliable because date numbering varies on different computer systems. In the 1900 date system, the following formula is another way to calculate months between two dates from the previous example (10-May-2015 and 20-Jul-2015):
`=DATEDIF(42134, 42205, "m")`

- Results of other functions. For instance, the following formula counts how many days there are between today's date and 20 May, 2015.
`=DATEDIF(TODAY(), "5/20/2015", "d")`

**Unit **- the time unit to use when calculating the difference between two dates. By supplying different units, you can get the DATEDIF function to return the date difference in days, months or years. Overall, 6 units are available, which are described in the following table.

Unit | Meaning | Explanation |

Y | Years | Number of complete years between the start and end dates. |

M | Months | Number of complete months between the dates. |

D | Days | Number of days between the start date and end date. |

MD | Days excluding years and months | The date difference in days, ignoring months and years. |

YD | Days excluding years | The date difference in days, ignoring years. |

YM | Months excluding days and years | The date difference in months, ignoring days and years. |

Hopefully, the above information has been helpful to understand the basics. And now, let's see how you can use the Excel DATEDIF function to compare dates in your worksheets and return the difference.

If you observed DATEDIF's arguments carefully, you've noticed that there exist 3 different units for counting days between the dates. Which one to use depends on exactly what your needs are.

Supposing you have the start date in cell A2 and the end date in cell B2 and you want Excel to return the date difference in days. A simple DATEDIF formula works just fine:

`=DATEDIF(A2, B2, "d")`

Provided that a value in the start_date argument is less than in end_date. In case the start date is greater than the end date, the Excel DATEDIF function returns the #NUM error, as in row 5:

If you are looking for a formula that can return the date difference in days as either a positive or negative number, simply subtract one date directly from the other:

`=B2-A2`

Please see How to subtract dates in Excel for full details and more formula examples.

Supposing you have two lists of dates that belong to different years and you wish to calculate the number of days between the dates as if they were of the same year. To do this, use a DATEDIF formula with "YD" unit:

`=DATEDIF(A2, B2, "yd")`

If you want the Excel DATEDIF function to ignore not only years but also moths, then use the "md" unit. In this case, your formula will calculate days between two dates as if they were of the same month and the same year:

`=DATEDIF(A2, B2, "md")`

The screenshot below demonstrates the results, and comparing it with the screenshot above can help understand the difference better.

As you probably noticed, the Excel DATEDIF function does not have a special unit to calculate date difference in weeks. However, there is an easy workaround.

To find out how many weeks there are between two dates, you can use the DATEDIF function with "D" unit to return the difference in days, and then divide the result by 7.

To get the number of **full weeks** between the dates, wrap your DATEDIF formula in the ROUNDDOWN function, which always rounds the number towards zero:

`=ROUNDDOWN((DATEDIF(A2, B2, "d") / 7), 0)`

Where A2 is the start date and B2 is the end date of the period you are calculating.

Similarly to counting days, the Excel DATEDIF function can compute the number of months between two dates that you specify. Depending on the unit you supply, the formula will produce different results.

To count the number of whole months between the dates, you use the DATEDIF function with "M" unit. For example, the following formula compares the dates in A2 (start date) and B2 (end date) and returns the difference in months:

`=DATEDIF(A2, B2, "m")`

To avoid such errors, you may force Excel to always perceive an older date as the start date, and a more recent date as the end date. To do this, add a simple logical test:

`=IF(B2>A2, DATEDIF(A2,B2,"m"), DATEDIF(B2,A2,"m"))`

To count the number of months between the dates as if they were of the same year, type "YM" in the unit argument:

`=DATEDIF(A2, B2, "ym")`

As you see, this formula also returns an error in row 6 where end date is less than the start date. If your data set may contain such dates, you will find the solution in the next examples.

An alternative way to calculate the number of months between two dates in Excel is using the MONTH function, or more precisely a combination of MONTH and YEAR functions:

`=(YEAR(B2) - YEAR(A2))*12 + MONTH(B2) - MONTH(A2)`

Of course, this formula is not so transparent as DATEDIF and it does take time to wrap your head around the logic. But unlike the DATEDIF function, it can compare any two dates and return the difference in months as either a positive or negative value:

Notice that the YEAR/MONTH formula has no problem with calculating months in row 6 where the start date is more recent than the end date, the scenario in which an analogues DATEDIF formula fails.

For example, in row 7 in the screenshot above, the DATEDIF formula returns 0 because a complete calendar month between the dates has not elapsed yet, while YEAR/MONTH returns 1 because the dates belong to different months.

In case all of your dates are of the same year, or you want to calculate months between the dates ignoring years, you can the MONTH function to retrieve the month from each date, and then subtract one month from the other:

`=MONTH(B2) - MONTH(A2)`

This formula works similarly to Excel DATEDIF with "YM" unit as demonstrated in the following screenshot:

However, the results returned by two formulas differ is a couple of rows:

- Row 4: the end date is less than the start date and therefore DATEDIF returns an error while MONTH-MONTH yields a negative value.
- Row 6: the dates are of different months, but the actual date difference is just one day. DATEDIF returns 0 because it calculates whole months between 2 dates. MONTH-MONTH returns 1 because it subtracts the months' numbers from each other ignoring days and years.

If you followed the previous examples where we calculated months and days between two dates, then you can easily derive a formula to calculate years in Excel. The following examples can help you check if you got the formula right :)

To find out the number of complete calendar years between two dates, use the old good DATEDIF with the "Y" unit:

`=DATEDIF(A2,B2,"y")`

Notice that the DATEDIF formula returns 0 in row 6, although the dates are of different years. This is because the number of full calendar years between the start and end dates equals to zero. And I believe you are not surprised to see the #NUM! error in row 7 where the start date is more recent than the end date.

An alternative way to calculate years in Excel is using the YEAR function. Similarly to the MONTH formula, you extract the year from each date, and then subtract the years from each other:

`=YEAR(B2) - YEAR(A2)`

In the following screenshot, you can compare the results returned by the DATEDIF and YEAR functions:

In most cases the results are identical, except that:

- The DATEDIF function calculates complete calendar years, while the YEAR formula simply subtracts one year from the other. Row 6 illustrates the difference.
- The DATEDIF formula returns an error if the start date is greater than the end date, while the YEAR function returns a negative value, as in row 7.

To count the number of complete years, months and days between two dates in a single formula, you simply concatenate three DATEDIF functions:

`=DATEDIF(A2, B2, "y") &" years, "&DATEDIF(A2, B2, "ym") &" months, " &DATEDIF(A2, B2, "md") &" days"`

If you'd rather not display zero values, you can wrap each DATEDIF in the IF function as follows:

`=IF(DATEDIF(A2,B2,"y")=0, "", DATEDIF(A2,B2,"y") & " years ") & IF(DATEDIF(A2,B2,"ym")=0,"", DATEDIF(A2,B2,"ym") & " months ") & IF(DATEDIF(A2, B2, "md")=0, "", DATEDIF(A2, B2, "md") & " days"`

The formula displays only non-zero elements as demonstrated in the following screenshot:

In fact, calculating someone's age based on the date of birth is a special case of calculating date difference in Excel, where the end date is today's date. So, you use a usual DATEDIF formula with "Y" unit that returns the number of years between the dates, and enter the TODAY() function in the end_date argument:

`=DATEDIF(A2, TODAY(), "y")`

Where A2 is the birth date.

The above formula calculates the number of complete years. If you'd rather get the exact age, including years, months and days, then concatenate three DATEDIF functions like we did in the previous example:

`=DATEDIF(B2,TODAY(),"y") & " Years, " & DATEDIF(B2,TODAY(),"ym") & " Months, " & DATEDIF(B2,TODAY(),"md") & " Days"`

And you will get the following result:

To learn other methods of converting a birthdate to age, check out How to calculate age from date of birth.

As demonstrated in the first part of this tutorial, Excel DATEDIF is quite a versatile function suitable for a variety of different uses. However, there is one significant drawback - it is undocumented by Microsoft, meaning, you won't find DATEDIF in the list of functions nor will you see any argument tooltips when you start typing a formula in a cell. To be able to use the DATEDIF function in your worksheets, you have to remember its syntax and enter all the arguments manually, which might be a time-consuming and error-prone way, especially for beginners.

Ultimate Suite for Excel changes this radically as it now provides the *Date & Time Wizard* that can make almost any date difference formula in no time. Here's how:

- Select the cell where you want to insert the formula.
- Go to the
*Ablebits Tools*tab >*Date & Time*group, and click the**Date & Time Wizard**button:

- The
*Date & Time Wizard*dialog window shows up, you switch to the**Difference**tab and supply data for the formula arguments:- Click in the
**Date 1**box (or click the*Collapse Dialog*button to the right of the box) and select a cell containing the first date. - Click in the
**Date 2**box and select a cell with the second date. - Select the desired unit or combination of units from the
**Difference in**drop-down menu. As you do this, the wizard lets you preview the result in the box and the formula in the cell. - If you are happy with the preview, click the
**Insert formula**button, otherwise try different units.

- Click in the

For example, this is how you can get the **number of days** between two dates in Excel:

Once the formula is inserted in the selected cell, you can copy it to other cells as usual by double-clicking or dragging the fill handle. The result will look similar to this:

To present the results in the most suitable way, a few more additional options are available:

**Exclude years**and/or**exclude months**from calculations.- Show or do not show
**text labels**like*days*,*months*,*weeks*, and*years*. - Show or do not show
**zero units**. - Return the results
**as negative values**if Date 1 (start date) is greater than Date 2 (end date).

As an example, let's get the difference between two dates in years, months, weeks and days, ignoring zero units:

Apart from speed and simplicity, the Date & Time Wizard provides a few more advantages:

- Unlike a regular DATEDIF formula, an advanced formula created by the wizard does not care which of the two dates is smaller and which is larger. The difference is always calculated perfectly even if Date 1 (start date) is greater than Date 2 (end date).
- The wizard supports all possible units (days, weeks, months and years) and lets you choose from 11 different combinations of these units.
- The formulas the wizard builds for you are normal Excel formulas, so you are free to edit, copy or move them as usual. You can also share your worksheets with other people, and all formulas will remain in place, even if someone does not have the Ultimate Suite in their Excel.

If you are curious to try the Date & Time Wizard in your worksheets, you are welcome to download 14-day trial version. And if you like the tool and decide to get a license of the Ultimate Suite, be sure to grab the **15% off** coupon code that we provide exclusively to our blog readers: AB14-BlogSpo.

This is how you compute the difference between two dates in various time intervals. Hopefully, the DATEDIF function and other formulas you've learned today will prove useful in your work. Anyway, I thank you for reading and invite to check out other tutorials to working with days and time in Excel.

Excel formulas
CSV
Excel functions
Print
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Updates
Conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 236 Responses to "Excel DATEDIF - calculate difference between two dates in days, weeks, months or years"

Hi, good day.

I have a problem to calculate my rental in different period and rate.

Eg.

1st - 7th year : 14.09.09 - 13.09.16

8th - 10th year: 14.09.16 - 13.09.19

1st - 7th year : $1,000

8th - 10th year: $750

1st - 7th year : Splitting percentage 20%

8th - 10th year: Splitting percentage 50%

How to converting the above in If formula in excel?

Hi,

How can I calculate together month and year in excel macro?.

e.g.

01-Jan-2017 (Textbox 1)

01-Feb-2017 (TextBox 2)

Result: 0.1 (Textbox 3)

Thank you in advance.

Hello,

I need to create an elapsed days formula from a start date to today's date for submittals. When the submittal is returned I would like the formula to show elapsed days from the start date to the end date (received) and not today's date.

Kind regards.

Hi, I'm trying to find a formula calculating days that has passed since..

For instance, how many days have passed (aged) since date of entry. This will be updated based on an input calendar date.

Is this possible?

hello

I'm trying to figure out, how to calculate no. days between two dates for a particular product which is repeating at different dates.

plz help me out..

thanks.

How Two or Five Period of Years, Month, Day Example:

01 September 1994 31 October 1994 0Years, 1Months, 30days

04 September 1995 30 November 1995 0Years, 2Months, 26days

01 December 1995 30 May 1996 0Years, 5Months, 29days

Total: ______ _______ _____

Hello Svetlana, Good day

I'm trying to calculate date difference is days, hours and minutes so there are 2 dates start and end date. Start Date (Date Value) and End Date (Blank or have formula). Any support would be greatly appreciated.

Dear,

Really, it is very help full because sometimes if we don't get in touch with these formulation, can be washed from brain.

How to calculate number of days for the below dates

09/04/2017 12:50 20/04/2017 11:55

and as soon as the date cross the time of the start date, one day should be added, how to do it?

how to find the difference in dates in months so that lets say employees joining before 15th of the month are considered to be working for 1 extra month and employees joining after 15th are not considered? Is it feasible?

I need between date different by this below format

Start Date 19.02.1989

End Date 01.02.2017

By right click,format cells,custom,scroll and pick up mm/dd/yyyy and modify it as per ur requirement.

Hi I am trying to automatically get column names in excel basis start date and duration as input. For example if I fill start date as 1-Jan-16 and Duration as 3 years or 36months it should automatically populate columns like Jan-16, Feb-16, Mar-16 .......Dec-18.

Great instructions! on negative dates code for between two dates! Best on the web.:) Thank you!

Hi,

Need a help. I want to calculate the no. of days between two dates by considering that only "sunday" is weekly off & rest 6 days are working.

Pl advice.

I need a formula that gives me the number of days weeks and months between two dates. (Excluding weekends)

I am trying to calculate the value of a rental tool per day week and month. Given the following rates : per day $10 per week $40 and month $150

Thank you!

Cec:

Take a look at this article here on AbleBits. I believe it will provide the answer you want.

https://www.ablebits.com/office-addins-blog/2015/05/20/excel-workday-networkdays-functions/#excel-NETWORKDAYS

These formulas are not working in my PC, I am using Excel 2013, and already checked all formulas are correct, surprised to see the error #value

Check the date format. This has to be MM/DD/YYYY

Hi

I would like to know some questions the following case.?

I have start date and end date for some contracts.

I used these formula.

=DATEDIF("Start Date", "End Date", "d")

I also need to know remaining date (start from open the file date)

So, I used these formula

=DATEDIF(TODAY(), "5/20/2015", "d")

But, Contract start date aren't start when i open the day. So.. it is problem for me.Please tell me how to solve these formula?

i hope you answer to me.

Thanks & Best Regards,

Wint Wah

Hi,

I am using Excel 2010 and am trying to work out a formula to work out the difference between two dates

Could you please help?

Thank you

=DATEDIF(B2,C2+1,"d")

Please try this one. In B2 start date & in c2 end date.

I see you add the +1 in the formula. I was wondering about this. I have accounted for this in the past, but everything shows that the number of actual days is apparently one less. Example: 01-NOV-2017 and 30-NOV-2017, no matter what function you use, the answer comes up 29 days instead of 30.

I usually add the +1 at the end of the formula, but it should come out the same.

It seems like the functions do not take into account the first day as if you really didn't meant to count it.

dateif doesn't exists

Hi Alex,

Dateif doesn't exist, but DateDif does :) However, DATEDIF in an undocumented function, which is why it does not appear in the list of formulas, so you will have to type a whole formula manually.

YAS! thank you - dateif didn't work for me, but datedif did. thank you :-)

Hii

I have needed a formulla, if a employee is joining date and leaving date would be >4 years and 6 months then he will get 50% bonus otherwise he will not eligible.

please help me.

I need exactly the same calculation. Have been using =datedif(a1,today()"m")>=6, however it is not the TODAY as reference but another date on the spreadsheet.

so i have got date 1, date 2, and I need to highlight date 2 only if it is over 6 months older than date 1 if that makes sense.

Hi,

Suppose we need to calculate Date of Retirement using available Date of Births of Employees. How to write a single formula for calculating Retirement Date which should be calculated depending following three conditions - If Public Sector Employee then Retirement Age should be 60 yrs, If Private Sector Employee the Retire Age should be 58 yrs and for Pensioners should be 70 yrs

Thanks

The most relevant unit of time measure for me is months between start and ending dates. Excel has a function, DATEDIF, which on surface sounds simple:

=DATEDIF(‘Start Date’,’End Date’,M) will report the number of complete months between dates.

That works 95% of the time but sometimes contracts cross leap years or they don’ start on the 1st day of the month or end on the last day of month.

Here is my solution

=ROUND(IF(TEXT(YEAR('Start Date'),0)&TEXT(MONTH('Start Date'),0)=TEXT(YEAR('End Date'),0)&TEXT(MONTH('End Date'),0),-1,DATEDIF(EOMONTH('Start Date',0)+1,EOMONTH('End Date',-1)+1,"M"))+(DATEDIF('Start Date',EOMONTH('Start Date',0),"D")+1)/DAY(EOMONTH('Start Date',0))+(DATEDIF(EOMONTH('End Date',-1),'End Date',"D"))/DAY(EOMONTH('End Date',0)),2)

I know that it looks wordy but I store it in a most used functions worksheet so I can copy it into my worksheet.

Below is sample of what it answers

Start Date End Date Real Months

1/1/2017 12/31/2017 12.00

1/1/2017 1/2/2017 0.06

1/5/2017 12/31/2021 59.87

1/1/2017 2/28/2020 37.97

1/1/2017 2/29/2020 38.00

3/15/2017 3/14/2018 12.00

1/5/2017 11/5/2017 10.04

3/15/2017 11/5/2017 7.72

3/1/2018 2/28/2019 12.00

3/1/2018 2/29/2020 24.00

3/1/2018 2/28/2020 23.97

I have not found a bug is my most recent version of this function.

can any one help me for Excel formula with using IF STATEMENT for calculate tax 5% on OR BEFORE dt 14/9/2017 & 6% if dt 15 or after 15/9/2017

A B C D

1 DATE AMOUNT 5% TAX 6% TAX

2 5/9/2017 2500 ?Formula ?Formula If date A column less than

3 14/9/2017 1000 15/9/2017 tax 5% oterwise 6%

4 15/9/2017 1000

5 26/5/2017 1000

6 15/10/2017 7000

I'm trying to do this in excel but it doesn't give me the option for DATEDIF, only DATE or DATEVALUE...so I'm not sure what to do. Do you guys have a different formula that I can use without that function?

Weird because I have the newest version...

The datedif function no longer exists inf Office 2016. As usual, when the imbeciles at Microsoft "improve" a product, they remove useful functions.

Hi Wayne,

DATEDIF still works in Excel 2016. But it is undocumented, meaning you won't find it in the list of functions. To use DATEDIF in your formulas, you need to remember its syntax and type all the arguments manually.

I am using the datedif function to calculate years of service for employment. I have that part down with the exception of employees that had a break in service. How do I add their previous years served before their break in service to their current years of service?

Example:

Current Date of Hire: 9/25/1999 to Present

Current Time in Service: 18Y, 1M

Previous Date of Hire: 5/23/1987

Previous Termination Date:9/20/1991

Previous Time in Service: 4Y, 3M

How to I create a formula to add the current time in service along with the previous time in service?

How to calculate time in excel such as

31/10/17 10:45:00

01/11/17 02:15:21

Please advise us above query

Hello, Ahmer,

Please try the following formula:

=DAYS("01/11/17 02:15:21"-"31/10/17 10:45:00",0)*24 + HOUR("01/11/17 02:15:21"-"31/10/17 10:45:00") & " hours " & MINUTE("01/11/17 02:15:21"-"31/10/17 10:45:00") & " minutes " & SECOND("01/11/17 02:15:21"-"31/10/17 10:45:00") & " seconds"

Hope it will help you.

Hi! Thanks for these instructions - the site/tutorials are great and I've learned a lot! I was wondering if you had any tips for me:

I am trying to display the date, that is x number of days after Jan 1.

I used:

A1 =datedif("01/01/2017","11/27/2017", "d")

to display how many days it has been since the start of the year.

I am now trying to display the date 30 days prior.

I was able to find the day number this year by:

=datedif("1/1/17", A1, "d")- 30

For instance, this equals 300. How do I display the 300th day of the year (i.e. October 27)?

Thanks!

Hello,

Please try the following formula:

=DATE(2017,1,300)

Hope it will help you.

I want to do the same but count the number of months going forward from say Aug to May in that format... Any thoughts?

Hello,

If I understand your task correctly, please try the following formula:

=DATEDIF(DATE(2016,8,31),DATE(2017,5,1),"M")

Hope this will help you!

I find the date functions flawed as they take an extra day away from the result.

01-NOV-2017 to 30-NOV-2017 should be 30 days, but every calculation I use shows 29. Why are the date functions missing a day?

I have a project in which employee should be sent medical test for evey 3years(i.e.one day before completion of 3years) which formula should I use.ex...ajay medical test on 1/01/2014 next check up will be on 31/12/2017... Eagerly waiting for reply

Hello,

Please try the following formula:

=TEXT(EDATE(A1,12*3)-1,"dd/mm/yyyy")

Hope it will help you.

Here's my version:

=

IF(DATEDIF(A2,B2,"y")>1,DATEDIF(A2,B2,"y")&" years, ",

IF(DATEDIF(A2,B2,"y")=1,DATEDIF(A2,B2,"y")&" year, ",

IF(DATEDIF(A2,B2,"y")1,DATEDIF(A2,B2,"ym")&" months, ",

IF(DATEDIF(A2,B2,"ym")=1,DATEDIF(A2,B2,"ym")&" month, ",

IF(DATEDIF(A2,B2,"ym")1,DATEDIF(A2,B2,"md")&" days",

IF(DATEDIF(A2,B2,"md")=1,DATEDIF(A2,B2,"md")&" day",

IF(DATEDIF(A2,B2,"md")<1,"")))

My previous post got scrambled in translation, hope this one works:

=

IF(DATEDIF(A2,B2,"y")>1,DATEDIF(A2,B2,"y")&" years, ",

IF(DATEDIF(A2,B2,"y")=1,DATEDIF(A2,B2,"y")&" year, ",

IF(DATEDIF(A2,B2,"y")1,DATEDIF(A2,B2,"ym")&" months, ",

IF(DATEDIF(A2,B2,"ym")=1,DATEDIF(A2,B2,"ym")&" month, ",

IF(DATEDIF(A2,B2,"ym")1,DATEDIF(A2,B2,"md")&" days",

IF(DATEDIF(A2,B2,"md")=1,DATEDIF(A2,B2,"md")&" day",

IF(DATEDIF(A2,B2,"md")<1,"")))

Hi, I got a problem in calculating for year month. I would like to add one month for every month.

Example:20Yr(s) 4 Mth(s)

(I would like to auto add one month for the example. Please give me the formula).

thanks.

Hi,

How to calculate difference in two dates in days when the date is selected/inserted from calendar through Microsoft Date and Time Picker Control.

This formula is not working =DATEDIF(A2, B2, "d") because it is not considering cell to the cell where date is selected from picker.

Thanks

What about if you use the month of February. If you deduct 28 feb 2018 and 31 Jan 2018 it will be zero month

Hi there. Newbie to Excel, so no expert by any stretch. I'm trying to work out how to do a formula to count days within a month only. I have two columns titled "Start Date" and "Finish Date". For example. Start Date is 22 Jan 18 and Finish Date is 11 Feb 18. Is there a formula to count the number of days (within those two columns) for the month of January 2018 only? It would equal 9 days. I then need another formula, same example as above, to calculate the days that would be returned for February 2018 only, ie. would equal 10 days. The total amount of lines within the speadsheet could be anywhere up to 500 lines. I have found formulas to count the total days, but not exactly for a month only. Hope that makes sense. Thanks.

how to set age limit for ex: 25.3.1978 to 15.4.1983, if age is between 35 to 40, i have to set 35 how to do it

Hi,

Can someone help me to that I want to find the days in between my start date & today date.

I tried

=DATEIF(today(), “3/6/2018” , “d”)

But the result is #NAME?

Thanks!

Hi Mavis,

Please first note that you have a typo in the function name. Besides, if your start date is prior the today's date, you need to change the position of the formula arguments. Thus, the correct formula should look like this:

=DATEDIF("3/6/2018",TODAY(),"d")

Hope this will work for you.

Hi,

This formula =NOW()-A1 does not work for same day calculation:

if today is 18 Mar 2018, the result shows a month!

For example A1 is 18 Mar 2018 07:10:00PM

I want to equal 1 year to date

hi there, can someone help me, i need help in column in where it would say "ready" if its within a year from today's date and the other will say "under construction" if its still over a year from today's date.

PROJECT TO DATE READY/UNDER CONSTRUCTION

SA December 31, 2012 April 11, 2018

SB June 30, 2013 April 11, 2018

SC June 30, 2013 April 11, 2018

SD June 30, 2013 April 11, 2018

SE December 31, 2013 April 11, 2018

SF March 31, 2014 April 11, 2018

SG January 31, 2015 April 11, 2018

SH February 28, 2015 April 11, 2018

SI June 30, 2015 April 11, 2018

SJ June 30, 2015 April 11, 2018

SK October 31, 2015 April 11, 2018

SL December 31, 2015 April 11, 2018

SM March 31, 2016 April 11, 2018

SN June 30, 2016 April 11, 2018

SO November 30, 2016 April 11, 2018

SP December 31, 2016 April 11, 2018

SQ March 31, 2017 April 11, 2018

SR June 30, 2017 April 11, 2018

SS July 31, 2017 April 11, 2018

ST August 31, 2017 April 11, 2018

SU February 12, 2018 April 11, 2018

SV February 28, 2018 April 11, 2018

SW September 30, 2018 April 11, 2018

SX October 31, 2018 April 11, 2018

SY November 30, 2018 April 11, 2018

SZ January 31, 2019 April 11, 2018

RA May 31, 2019 April 11, 2018

I need to find the difference between two dates in weeks and days. Can you help me?

Fawn:

Where start date is in A1 and end date is in B1 enter this in C1 for days

=DATEDIF(A1,B1,"D")

enter this in D1 for whole weeks

=ROUNDDOWN(DATEDIF(A1,B1,"D")/7,0)

How do I calculate the number of days between two dates in two columns?

Helen:

Where the oldest date is in A1 and the newest date is in B1, put this in C1:

=DATEDIF(A1,B1,"D")

21-09-1981 36 Years, 8 Month,17 Days

Date is blank 118 Years, 5 Month,8 Days ( I want to show this colum empty)

118 Years, 5 Month,8 Days

118 Years, 5 Month,8 Days

I issued this formula for date calculation DATEDIF($F5,TODAY(),"y")&" Years, " &DATEDIF($F5,TODAY(),"ym")&" Month,"&DATEDIF($F5,TODAY(),"md")&" Days"

When date cell is empty formula cell show blank please help solve to this problem

Hi

Maybe someone can help me, I need to determine the difference in weeks for example: the difference between week year - week year (10 2018 - 22 2018)= 12

Thakns

Luis:

You need to enter the date in a format your version of Excel recognizes as a date like 1/1/18 is January 1st 2018 in my version.

If your version supports DATEDIF, then you can enter this formula in the display cell of your choice

=ROUNDDOWN(DATEDIF(A47,D47,"D")/7,0)

So, if A47 holds the date 3-Apr-14 and D47 holds the date 1-Sep-18 this formula will return 230.

Be sure to format the cells as dates.

You can change the A47 and D47 to the cell addresses that suit you.

I have no datedif function in Excel 2013, but instead have datediff function which is different. Is there a method that I can use datedif function in VBA Excel 2013?

I have a list of dates for car payments occurring bi-weekly. My goal is to count any month that contains an instance of 3 dates. I have no idea how to make a formula for this. I've been Googling this for an hour and can't find an answer.

Example:

2018-09-13

2018-09-27

2018-10-11

2018-10-25

2018-11-08

2018-11-22

2018-12-06

2018-12-20

2019-01-03

2019-01-17

2019-01-31

2019-02-14

There are three car payments in January. I need to create a countifs for recurring months exceeding two date entries.

This is literally just one column with dates in it under a heading called "Car Payments." In a separate area, I need to use a function to show which months appear more than twice.

Does anyone know how to do this?

Rini:

Where the dates are in the range O11:O22 use this formula to count the number of times January 2019 appears in the list.

=COUNTIFS($O$11:$O$22,">=1/1/2019",$O$11:$O$22,"<=1/31/2019")

You can enter, "Jan 2019 Car Payments" in an empty cell to the left or right of the cell that holds this formula.

Then you'll see the number of time Jan 2019 appears in that range. By copying this formula and pasting it into another cell and changing the dates you can do the same thing for all the dates for which you're interested in getting a count.

Note I've got the range in an absolute reference by using the dollar signs. If you want your range to extend beyond the 11 cells in that range change the $O$22 to a different address.

Be mindful of how you copy and paste this formula because there should not be a break after the COUNTIFS function.

I have to be able to calculate the total number of years, months, and days of experience for many employees. I have used the DATEDIF function to calculate the time elapsed between the dates for each individual job. However, I am trying to figure out a way of adding the resulting figures to come up with a comprehensive time calculation (ie. enter the start and end dates to come up with a calculation that the employee worked 1y, 2m, 28d at one job, and 3y, 5m, 7d at a second job, and then have a separate field that is able to tell me that the employee has a total of 4y, 8m, 5d of experience).

Did you get an answer for this? I'm trying to do the same thing.

Thanks, it was very helpful.

not working with office 2007, please help

How to calculate number of exists days for the below dates

09/04/2018 12:50 20/04/2017 11:55

Please solve my problem as soon as your free time Thanks... :)

I need to calculate a % of a task completed within 5 days. In brief:

* Column J = Notified Date

* Column M = Completed Date

* Column N = work days it took to complete task

KPI is 5 Days, so I need to work out from Column N if this was within 5 days, ie. what the % rate was. If 5 days or less, then response would be 100%. If less than 5 days, then display the % rate.

Is there a formula that can calculate this %. Thanks :)

I have a date i.e 22-Mar-1968 and I want to add in this date only 23 year. what will be the formula in excel. please guide me.

Please reply my question posted on 12 February 2019. With Thanx

hi

i am not able to use this formula in windows10

=DATEDIF(M89, N89, "y") &" years, "&DATEDIF(M89, N89, "ym") &" months, " &DATEDIF(M89, N89, "md") &" days" it showing #value!

and same for this also

=(MID(T88,4,2)&"/"&LEFT(T88,2)&"/"&RIGHT(T88,2))+0

kindly help on it

I'm trying to track evaluations. I want the same month and day as their date of hire, but this year to be included. For example, date of hire: 2/14/1997, next eval: 2/14/2019. Is there a formula for this?

Hi,

I have confusion :- my start date is - March 01, 2019 and end date - today (March 27, 2019) if i set formula it is calculating 26 Days but if i count it manually it is calculation 27 Days. I don't understand what is my problem.... Please provide me a solution......

Calculate number of date between dates and days in Excel

Hi guys,

Can you help me with something ?

i want to compare 2 calendar dates in excel , for example:

DATE 1 - DATE 2

10/3/2017--2/6/2020

1/17/2019--1/17/2020

9/25/2017--1/25/2019

3/21/2018--3/21/2019

as some of the days have been done in the past I would need to compare the dates between themselves and between "today"'s date to have am update of the process any time ;as this dates represent completion days of our colleagues training.

DATE 1 is the date when the training has been completed

DATE 2 is the due date for the next training

If the date is DATE 2 < TODAY by 3 moths range it will be highlighted with red

if the date is DATE 2 TODAY it will be highlighted with amber/yellow

if the date is DATE 2 > TODAY it will be highlighted in green

can you guys help me :)

How to write at a time if conditions and eomonth in excel. I am write a one condition =if(C1=“FALSE”,EOMONTH(D3),B40,””) but this is wrong plz tel me correct answer (C1=Cell address, D3=Cell address, B40=Cell address there another one date)

01/07/2019 20:30 to 08/07/2019 09:55

how to calculate time

Dear,

I have same question like Rab nawaz.

I have count different data like as

01 September 1994 31 October 1994 0Years, 1Months, 30days

04 September 1995 30 November 1995 0Years, 2Months, 26days

01 December 1995 30 May 1996 0Years, 5Months, 29days

Dear,

I have same question like Rab Nawaz.

I have to count different data, like as shown on the right side here (only count Years, Months and days)

01 September 1994 31 October 1994 0Years, 1Months, 30days

04 September 1995 30 November 1995 0Years, 2Months, 26days

01 December 1995 30 May 1996 0Years, 5Months, 29days

Thank You beforehand.

If i have start date & end date available in column so mentioned formula is use full to calculate the days, but if any column only start date mention and end date is not available & i want to know how many days have passed from start date. any formula where we can use both formula's to calculate the same.

Start Date & date : =DATEDIF(H2,I2,"D")

only Start date : =TODAY()-H2

How we can use both formulas

Hello,

Did you recieve a response to this query as I am looking for a formula to do the exact same?

Someone please can guide me how i can extract required text from below data as i need Model name i.e. Smsng, ID Iphone x, Code125, EMEI, EMI, ID.

Please guide.

Code125 EMEI2546 Alb Dist. Iphone x user Alph.

ID36520 2564MEI Smp. Lng Smsng met 2456

ID36520 2564MEI Colt. Conc. local mkv 365

ID36520 EMEI2546 Alb Dist. Smsng mkl 25

Code125 2564MEI Smp. Conc. Iphone x user 14

Code125 2564MEI Smp. Lng local met 896

ID36520 2564MEI Alb Conc. Iphone x mkv 125

Code125 2564MEI Smp. Dist. Smsng mkl 35

ID36520 EMEI2546 Alb Lng local user 46

ID36520 2564MEI Smp. Dist. Smsng met 125

Code125 EMEI2546 Colt. Mnt. Iphone x mkv 400

Code125 2564MEI Alb Lng local user 600

Code125 EMEI2546 Alb Dist. Iphone x met 1915

ID36520 2564MEI Con Lng Smsng user 16

if i have a date range 10/01/2019 - 11/19/2019, what formula will calculate the number of days in the first month only? It would also need to consider if the date range is 10/01/2019 - 10/05/2019.

Many thanks,

B

In datedif formula the days should not come as "30"

eg.486 days = 1 year 3 month 30 days (in datedif formaula)

but we want the answer is 1 year 4 month .

Hi, I need a help to find Time Different of Two Dates with time but its should be with in two other Date and Times. Eg. END Time : 12/18/2019 23:16 - Start Time : 12/18/2019 21:16 but it would in bracket of available Time 12/18/2019 22:16

and 12/18/2019 22:26 . Ans: 0:10:00 ; How can I reach ?

Please send the the formula for following conditions

If the date is between 2 Jan 2019 and 1 July 2019 the result would be 1 Jan 2019

Hi guys,

how to i calculate between two years

example: 2years 4months and 5years 3 months

Hi,

I have 24 columns with Jan' 20 to Dec' 21 and having some values in rows now I wish to transfer values to another sheet in columns (Jan'20 to Dec'21) by adding days different for all rows like 1, 10, 15, 30, 45, 60, 75, 90 etc.

For Eg. If I have 20 in Jan'20 and need to add 45 days in Jan' 20 then another sheet should have 20 in Feb' 20 or I have to add 90 days then 20 should go to Mar' 20 or Apr' 20.

Please advise.

hello

please help

i create excel in a sheet

what i need is to some all items with the same date alone example whole 01/january alone and 02 january alone and finally add up between 01/january and 02/january together in the month of january

I used the DATEDIF function to calculate the difference between 2 dates but when the value is negative it displays error. How can I correct it?

Hello Aminu!

Please try the following formula:

=IFERROR(DATEDIF(A1,A2,"d"), DATEDIF(A2,A1,"d"))

Hope you’ll find this information helpful.

Hy if i have two dates i.e

01/04/2020 to 10/08/2020 then how to bifurcate difference between two dates into different months i.e in April month-30 days, May month 31days, June 30 days , July 31 days, Aug 10 days

Hello Dishant!

I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.

Hi

Presuming i have 2 dates 1 Apr 2019 in (C3) and 31 Mar 2020 in (D3). I want to calculate the duration of of service in Years, Months and Years. I used the following formula :

=DATEDIF(C3,D3,"y")& " Years, "&DATEDIF(C3,D3,"YM")&" Months and " &DATEDIF(C3,D3,"md")& " days"

The answer i got was (2 years 11 months and 30 days) I want the date to show as (3 years, 0 months and 0 Days)

How do i go around this?

Thank you

Tessie

Hello Tessie!

I am not sure if this is logical, but you can use the formula

=IF(DATEDIF(C3,D3,"md") < 30, DATEDIF(C3,D3,"Y")& " Years, "&DATEDIF(C3,D3,"YM")&" Months and " &DATEDIF(C3,D3,"md")& " days",DATEDIF(C3,D3+1,"Y")& " Years, "&DATEDIF(C3,D3+1,"YM")&" Months and " &DATEDIF(C3,D3+1,"md")& " days")

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hello. Can you please help me figure out how to count how many times multiple installations date ranges occur each in week of each year between two date columns for each installer?

I'm trying to count how many, let say "installs," occur per week between the start date and end date columns.

There is more than one row with start dates and end dates that overlap, and I'm trying to break it down by how many are overlapping in each week.

Column B & C are the start and end of the install. E and F are just part of my model.

For example, someone may have 44 installs per year, but how many are occurring each week at the same time.

I'm trying to show how many installs occur/overlap to the right of columns E and F via column G.

See tab SE Grid (DEB). I'm pretty sure the numbers are correct, but you can double-check.

Link to the spreadsheet with manual calculations: https://docs.google.com/spreadsheets/d/1UGQxHzjHi4UWMcT_4shbLt45n5KW2jzAjUe5Evz7JRg/edit?usp=sharing

I think the spreadsheet link below is close, but I see some discrepancies. Link to the spreadsheet with the formulas tried below: https://docs.google.com/spreadsheets/d/1krnLiVUTfXWIWh0PTVXqK9Zpy5lNegYHoUWGMTruI88/edit?usp=sharing

Here are some formulas I have tried:

1. =SUMIFS($H$3:$H$44,$G$3:$G$44, >=K3&)+SUMIFS($H$3:$H$44,$G$3:$G$44, "&K2,$A$2:$A$217,$H$2:$H$217,"=K3"},0))*ISNUMBER(MATCH($H$3:$H$44, {"=K4),--($G$3:$G$44"&WEEKNUM(N3),$L$3:$L$44,"="&D1,F5:F18,"<="&D2)