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

## Excel DATEDIF function - calculating date difference

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.

#### Excel DATEDIF function - syntax

The syntax of the Excel DATEDIF function is as follows:

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")`

**Note.**In your formulas, the end date must always be greater than the start date, otherwise the Excel DATEDIF function returns the #NUM! error.

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

## Excel DATEDIF formulas to calculate date difference in days

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.

#### Example 1. Count the number of days between two dates

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.

#### Example 2. Count days in Excel ignoring years

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.

**Tip.**To get the number of

**working days**between two dates, use the NETWORKDAYS or NETWORKDAYS.INTL function.

## Calculating date difference in weeks

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.

## How to calculate months between two dates in Excel

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.

#### Example 1. Calculate complete months between two dates (DATEDIF)

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")`

**Note.**For the DATEDIF formula to calculate months correctly, the end date should always be greater than the start date; otherwise the formula returns the #NUM error.

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"))`

#### Example 2. Calculate months between two dates ignoring years (DATEDIF)

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.

#### Example 3. Calculating months between two dates (MONTH function)

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.

**Note.** The results returned by DATEDIF and YEAR/MONTH formulas are not always identical because they operate based on different principles. The Excel DATEDIF function returns the number of **complete calendar months** between the dates, while the YEAR/MONTH formula operates on months' numbers.

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.

#### Example 4. Counting months between 2 dates ignoring years (MONTH function)

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.

## How to calculate years between two dates in Excel

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

#### Example 1. Calculating complete years between two dates (DATEDIF function)

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.

#### Example 2. Calculating years between two dates (YEAR function)

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.

## How to calculate date difference is days, months and years

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:

## DATEDIF formulas to calculate age in Excel

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.

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

Hi. i just wondering if I cannot send an attachment with regards to problem in calculating the days( dates). So that I can show you the spreadsheet.

They asking me to calculates the number days since the student say the exam. If the student has not yet sat the exam, instruct the formula to display a hyphen.

The formula that used is DATEDIF but my instructor said its wrong. I hope you can help me. Thank you. > Louie

Hi Louie,

We are by all means avoiding posting our email address anywhere because of spammers. So, can you please post the same question on our forum and attach your workbook. Our support team will do their best to help.

If I can send an attachment I mean. Thank you

Louie,

You can attach a file when posting a question on our forum. You just have to register (create a user account), then click the blue "Start New Topic" button in the upper right corner, and then click the Attach Files button in the lower left corner.

Hi Svetlana, I already posted my topic : Essential Function.

Hope you can help me. Thank you again > Louie

Let me know if you receive it love. Thank you

Louie,

You could use the following DATEDIF function for column F:

=IF($E5<>"", DATEDIF($E5, $F$3, "d"), "-")

However, there's a simpler way. You can find the workbook with the formulas on the following page, hopefully they will be approved :)

https://www.ablebits.com/forums/topic/23425-essential-functions/

Hi

Please give the formula in excel how do I convert a number in different row year month and day, tried my best but failed to get the solution. Suppose give me the solution below -

Day Year Month Day

400 ? ? ?

Regards

Sazedul

Hi, I'm trying to set up a table where the difference between dates is needed, however I need to be able to break this down into months (the table to heave headers for the months). For example, if we have a start date of 01/04/14 and an end date of 07/05/14, is there a formula I can use which will automatically give me 30 days in one cell for April, and 7 Days in another cell for May?

Hi Sam,

This is feasible, but the formula is not so easy to explain :) We've made an example for you and you can download it here.

Brilliant, thank you so much for your help!

Hi Svetlana,

I was looking for a similar Excel formula as Sam and found that this one will only work if the date range are in the same year.

How would the formula change if the dates were in different years? For example if I need the days totaled for April 2015, May 2015, and June 2015 from the date range of 23-Sep-2014 to 4-Dec-2015?

Would this be possible to do?

Thanks for your help!

Hi Lindsay,

We have improved the formula to work with different years. Here's is an example.

Hi Svetlana,

Your improved formula for Linday is great comment-162067.xlsx. However, is it possible to exclude the holiday/s in a particular month/year?

Thanks in advance.

Hi Gregg,

Regrettably, we do not see an obvious way to do this in a formula, most likely a VBA script would be needed.

Hi Gregg,

you can use the =NETWORKDAYS.INTL() formula to get the number of days and also exclude the holidays.

Regards.

I need my code to hide a row once the date entered is 30 days past current date. Also all the cells are not filled, the date will be enter later I don't want too see value errors in the blanks.

How can this be done?

I have a particular date for a particular task and if the date has past 2 days from the bench mark date then how can I put a formula to highlight that this particular task has been already passed 2 days so that I can chase for the same.

Hi Svetlana,

I used the formula "Datedif" both for Month and year, it's missed one Month or one Year. Eg. 01/Jan/2015 and 31/Dec/2015 the logical is 12 Months but the answer of the formula is 11 Months (Wrong).

How can this be done?

Thanks.

Regards

Abby

did you find the an answer , I am also facing the same issue. thanks

Hi abby & Svetlana,

did you get the answer, i also face the same issue.

start date is 1/7/15 and end date is 30/6/15.

the formula answer only 11 month. It suppose to be count as 12 month.

could you please share idea to counter the problem.

thank you.

I was facing the same problem of getting the correct month difference between 2 dates. For example:

30/09/2016 - 31/10/2016 - Datedif given me 1 month difference, but then

31/10/2016 - 30/11/2016 - Datedif given me 0 month differece.

This showed inconsistency of computation.

sir,any formula u have which calculate days among four dates.

How would you take the todays date in A1 subtract that from all dates posted in Column D and post the days till in Column G?

Hello Chadd,

Supposing that row 2 is the first row with dates, enter the following formula in G2, and then copy it down to other cells in column G:

=$D2-$A$1

Hi there,

I've been trying to find a formula which calculates the number of weeks and days from 2 dates.

Can you help?

Hello Adam,

Please use

=IF(INT((A2-A1)/7)>0,INT((A2-A1)/7)&" week(s) ","")&IF(MOD(A2-A1,7)>0,MOD(A2-A1,7)&" day(s)","")

A1 - start date, A2 - end date

I am trying to find a formula that will turn a certificate name cell green if a date is less than 9 months old, yellow if the date is between 9 and 10 months old, orange if it is between 10 and 11 months old and red if it is over 11 months old. this is for one year expiration but I want to apply it to 2, 3 and 4 year expiration dates as well.

Hello Brenda,

You can change a cell's font or background color by using Excel conditional formatting. And you can find a few examples of conditional formatting rules for dates in the following tutorial:

https://www.ablebits.com/office-addins-blog/2014/06/17/excel-conditional-formatting-dates/#highlight-date-range

Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.

Hello, Anita,

You can use the IF function for the end date function parameter. For example IF(ISBLANK(D2); TODAY(); D2)

=DATEDIF(K4,J4,"D") IS NOT WORKING IN MY EXEL CAN U HELP ME

DATE FORMAT IS SHOWN UNDER

14/05/2015 30/05/2015

Hello Svetlana....Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.

Hello, Anita,

You can use the IF function for the end date function parameter. For example IF(ISBLANK(D2); TODAY(); D2)

how to change date format 26/10/2015 in to 26-Oct-2015 using the excel formula.. kindly send me ans

Hello, Fjan,

Please try this formula:

=TEXT(A1,"dd-mmm-yyyy")

i am looking to find the formula of leave management system in excel. i have pick Date calender from developer option and drop in to 2 cells like C20 start date of leave and G20 End date of Leave. In other cell i have 30 casual leaves in 1 year now i am coming to the point when i will select start date of leave and end day of Leave the Automatically Cut leaves from the other 30 casual leave is it possible./.....

Or if like me you would like to see a dynamic formula that takes into account the differnce in multiple months for example then the formula could be adapted as follows:

=IF(DATEDIF(A2,B2,"y")=0,"",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"",DATEDIF(A2,B2,"ym")&IF(OR(DATEDIF(A2,B2,"ym")>1)," months "," month ")&IF(DATEDIF(A2,B2,"md")=0,"",DATEDIF(A2,B2,"md")&" days"))

This guidance was superb. But if I consider all months of 30 days (360 days for year) how can I calculate difference between two dates in days / months/ years etc?

DATE DATE DAYS

1/8/2016 4/7/2016 90.00 (=DATEDIF(C5,D5,"D")

In the above case 2016 Leap year where DATEDIF missing 29th February, 2016 i.e. output result should be 91 days and not 90 days

In case of manual dates calculation = 91 days is exact result.

Can any one help DATEDIF (days/month/year) in caese leap year.

Hello, Jagadeesh,

If you enter the year of 2015, you'll get 89, for 2016 you'll get 90. Hope this helps.

In my excel worksheet, I entered:

1/1/2016 12/31/2016 (=DATEDIF(C5,D5,"D") and it came up with 365 rather than 366. Please advise how to get it to count Feb 29,2016.

hi may i know how to count a set of datas in weeks

Hello, Vinn,

This info is described in the current article. Please see part Calculating date difference in weeks:

https://www.ablebits.com/office-addins-blog/2015/05/28/excel-datedif-calculate-date-difference/#weeks-between-dates

Hi

May i know how to calculate in date two between date,month and year ?Please give me example and formula.I wish u all the best.

Hi, I would like to have my result be in decimal form when calculating time between two dates. Example 3.7 years

Hello, KM,

Please use the formula below:

=ROUND((DATEDIF(A1,A2,"M")/12), 1) & " years"

Hi,

same question as KM, but on days, how can I get days in decimals, i.e., 1.47 days

Thanks

Hello, Chirag,

Please use this formula:

=ROUND(DATEDIF(A1,A2,"D") + (HOUR(A2-A1) / 24), 1) & " days"

Same Question only for Months. How can I get the number of months in decimal. I.e. 11.65 months?

very useful....and very helpful for all champs...

Date of join Present Date Year Month Day

22/01/2012 13/01/2016 3 11 135

I am facing the problem in Day cell. Why it shows 135 days in day cell ? Please solve this problem. I am using Excel 2007.

Hello, Nabin,

Please specify the formula you use.

Using that formula from above : =DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days"

Put 3/12/2015 in A1, and 1/11/2016 in A2. You'll get a result of 0 years, 9 months, 143 days. Why?

Make A2 1/17/2016 and you get 0 years, 10 months, 5 days.

Column A is formatted as Date, while the results column is General.

Excel 2007 SP3. Win7 Pro (same result on Server 2008 R2)

Hello, Robert,

Looks like a kind of bug with Excel 2007. Sorry, we haven't been able to reproduce it in Excel 2016.

Hi Maria,

I try to calculate for 12 month the annual leave for worker who work in my company example:

Januar, februar, March,April and worker used in januar with date 4 day in march used 6 days i lile to know total days with date start and end date diference can you help me please.

Agim

Hello,

I have the following situation: Sheet 1 contains a list of articles (code and name), with Start delivery date, End delivery date, and a promotional price which applies only between those dates.

Sheet 2: a database with all the articles delivered, date of delivery and delivery price.

I need the following: in sheet 2, I need to bring the promo price for a certain article from sheet 1, if the article was delivered between the dates (start and end delivery date).

Please help!

I need to calculate time difference for 500 different dates in column B and column A in year decimal point. Is there a formula that will calculate all 500 time difference and present it in column C?

Hi, I am trying to find out the difference between two dates. The dates refer to the expected end date and the actual end date, and I want to know how many have gone over time and how many days it has gone over. When I use DATEDIF above the dates where there would be a 'negative' result run as an error.

Hi Natalie,

If you are calculating the difference in days, then you can simply subtract one date from the other and get the result either as a positive or negative number.

If you don't want negative numbers, then you can use a nested If formula similar to this:

=IF(A2>B2, DATEDIF(B2,A2,"d"), DATEDIF(A2,B2,"d"))

Hi Cheysheva

wonder if you can guide me how to count date and time different for working hours only? (0900-1700)

your help would be greatly appreciated...

Hi there

I need assistance with a formula. Setting up the excel file to calculate the lead-time from when the product was ordered to when it was received, however the cell must over-ride so that the calculated figure is always divisible by 7 eg. actual lead-time calculated is 23 days, however new rule is to formulate it to the 7 day = 21 days

Need to count how many days in a contract will occur during a specific period of time. Example: contract runs 9/2/2014 - 8/31/2015 during the period of 9/1/2014 - 9/28/2014.

i want a formula to calculate interset on quarterly basis from the date of investment

Its Good

Thank you !

Hi I am using an incident/accident database.

I have the date of the accident in C1

The date returned to work L1

I want to count the number of weekdays missing however I want to count the days missing in the month that the accident happened and days carried over to the next month.

Eg if accident happened 29/01/2016 returned to work 05/02/2016

how may weekdays were missing in January?

how many weekdays were missing in February?

Thanks and hope this makes sense

Hi there,

I was wondering if there is a way to sort out financial years between 2 dates. EG 01/01/2013 to 19/10/2015. We know these dates span 4 financial years. Is there a way to allow excel to sort these dates so they appears like the below:

01/01/2013 to 30/06/2013

01/07/2013 to 30/06/2014

01/07/2014 to 30/06/2015

01/07/2015 to 19/10/2015

Thanks!

Hi friends,

I want to find age from TODAY() but date format is below in a cell

----------A--------------------!

Serial no. Date Time Age

8505642244 29-02-2016 11:30AM! ?

8505642249 26-02-2016 11:30AM ?

8505642444 20-02-2016 11:30AM ?

Hi friends,

I need to find age from TODAY() but date format is below in a cell(Serial no. Date Time) & (Age) and there needs to compare with current date and cell date into age.

=============================================

----------A-COLUMN-------------!--B-COLUMN--!

=============================================

Serial no. Date Time ! Age !

8505642244 29-02-2016 11:30AM ! ? !

8505642249 26-02-2016 11:30AM ! ? !

8505642444 20-02-2016 11:30AM ! ? !

=============================================

Can it possible? if yes, please share.

Thanks!

Guys, can anyone help me with this.

i have 2 dates Call confirmed - 01/02/2016 11:47 & Dispatched - 05/02/2016 13:50. i want to calculate how many days hours and minutes. the result should be in this format 0 days 0 hours 0 minutes. please help with this.

Hi guys,

I am working in big excel, it has more than 2M records, I am using PowerPivot. I am trying to aggregate the data by day. I have a lot of transaction in a day, I want to group that. I tried to use the following DAX but it doesn't seem to work...I am new for this...any tips?

=COUNT([column name],CALENDAR(2016,1,31.2016,1,31))

Parameters:

Item Start Date End Date Value

A 01-Apr-11 02-Feb-12 2

A 03-Feb-12 01-Mar-12 3

A 02-Mar-12 31-Dec-13 4

A 01-Jan-14 31-Jan-14 5

B 09-Jan-13 04-Apr-14 6

B 05-Apr-14 07-Feb-15 7

B 08-Feb-15 01-May-16 8

B 02-May-16 01-Jun-16 9

Item Date Value

A 30-Apr-12

A 05-Feb-12

A 30-Jan-14

B 07-Apr-14

B 20-Feb-15

Please help for for the above value column based on provided parameters.

Hello Svetlana...Good Morning....I'm trying to "correct" a formula that we need to calculate how to calculate date difference is days, months and years...so there are 2 dates start and end date. Currently the formula is =IF(A1="","", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y.")) Which is pretty good but when the data has a start date and "no" end date....I get a #NUM! error.

For example: 1) Start 1/1/2016 End 2/15/2016 ... I get a 14 days. 1 m.0 y. it is good.

2) Start 1/1/2016 End Blank .... I get a #NUM! error. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any support would be greatly appreciated.

Hello Nani,

You were looking at the right direction. You can add one more IF statement to your formula, which will calculate the difference between the start date (A1) and Today() if there's a date in A1 but B1 is empty:

=IF(AND(A1<>"", B1=""), DATEDIF($A1,TODAY(),"md")&" days. "&DATEDIF($A1,TODAY(),"ym")&" m."&DATEDIF($A1,TODAY(),"y")&" y.", IF(A1="", "", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y."))

HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)

Hi - can anyone help me with a formula,

I want to calculate the number of days between Today and Column M. I want to display positive and negative numbers but sometimes there is no date in column M.

If I use =TODAY()-M1, I get a #VALUE error if no date

If I use ==DATEDIF(Today(), B2, "d"), I get a #NUM error for negative output.

Please help!

I'm using the datedif formula to initially calculate days and then weeks (DATEDIF(X11,Y11,"d")/7. I calculated weeks for the entire year, 1/1/16-12/31/16, and came up with 52.14. However, I need to do it by month - and when I come up with a total number of weeks, using the same formula, the total for the year is 50.57 weeks. I'm off by 1.57 weeks. What am I doing wrong? This exercise is being used to determine temp costs.

Many thanks

I'm trying to calculate the amount of time in years someone has worked here but that number needs to change based on the date the excel sheet is opened. So if I open this sheet next month, the numbers will change to show the time worked calculated from their start date to whatever date I open the file next month. Likewise, if I open the file 6 months from now, the number of years worked will change accordingly to include the past 6 months. Not sure I'm explaining this well but I hope you understand.

Thanks in advance for your help.

I can't find DATEDIF function. It is only showing me DATE and DATEVALUE.

Hi Tujju,

DATEDIF is one of few undocumented functions in Excel, which don't appear either in the formula list, or when you start typing in the formula bar. To make a formula, you need to type all the arguments manually.

DATEIF calculates number of months between 1st Jan 2015 and 31 Dec 2015 as 11 months. How can I adjust the formulate to calculate the answer as 12 months. Thanking you very much for your assistance.

Hi Colin,

I had this issue also. Turns out the fix is quite simple, just add "+1" to the end of the formula. =DATEDIF(A1,B1,"m")+1

For some reason the format of the cell seems to randomly amend to date, but you can simply amend back to number if this happens.

Hope this helped!

Dan

Hi,

I get an error message each time I try to use datedif function. The error message says: "The formula you typed contains an error"

Can anyone help me with this?

Alfred

I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate moths post contract expiry date (negative) or how many moths to contract expiration date(positive)

I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate months post contract expiry date (negative) or how many months to contract expiration date(positive)

Reply

This is epic!

Working with this one:

How to calculate date difference is days, months and years

Is there a way to calculate the max, min, and average time?

Thank you so much!

thank you. very helpful.

THANK YOU !

The DATEDIF function does not appear in the default installation of MS Office 2010. Is there a way to import this function, or update Excel 2010 to add this function?

There is no way :( DATEDIF is one of few undocumented functions in Excel, and that is why it does not appear in the formula list.

5/25/2016 -8/31/2017 if between these two dates i want to find no of days in may then june and so respectively how do i do that. for example in may the number of days are 5 . i want the formula for this.

I am using this formula and the result is not correct. For example, I'm calculating the number of months between 3/26/2013 - 1/30-2016, which is logically 2.10. I use the formula =ROUND((DATEDIF(G2,J2,"M")/12), 1)and the result is 2.8. The cell G2 contains the date 3/26/2013 and J2 contains the date 1/30/2016. Where did I go wrong?

Hi Svetlana,

kindly help, i put different date in one column i need the date to date between days

For example Column A i give different date 1 to 10 cell now i need column B the different date to date days

Column A Column B

11-May-16

13-May-16 ?days

17-May-16 ?days

1-Jun-16 ?days

I have a question... that kind of go along these lines...

I have my formula: =DATEDIF(D3,TODAY(),"D")

This is giving me the aging that I desire...

The problem is, I want to be able to keep this aging... but I also want to stop the counter, when I plug in a completion date... of a given task...

Any thoughts~

Hi Shay,

I think you can use a formula similar to this:

=IF(E3<>"", DATEDIF(D3,E3,"d"), DATEDIF(D3,TODAY(),"d"))

Where E3 is the completion date. If there is a date in E3, the formula calculates the difference between D3 and E3, otherwise between D3 and today's date.

how are you,may you pliz send me a worked downloadable example on hw to calculate days between two dates.

Hi Svetlana

Could you please help me with the formula.

I have used data validation. All I want is if it says says. I want a date to be populated after 7 business days. If no I want the date after 5 business days to be populated in a cell.

HI

IN CONDITIONAL PARAMETER

01 APRIL 2015 TO 30 JUNE 2015 = IS 1ST QTR

01 JULY 2015 TO 30 SEPTEMBER 2015 = IS 2ND QTR

01 OCTOBER 2015 TO 30 DECEMBER 2015 = IS 3RD QTR

01 JANUARY 2015 TO 30 MARCH 2015 = IS 4TH QTR

IF WE PUT THE DATE AS WE DESIRE IT WILL AUTOMETIC PRINT/DISPLAY IT QTR

I have simple formula to calculate number of days between dates =DAYS(G266,C266). If there is no date yet in G column the result in the H column with the formula is -42412 or #VALUE!. What formula in the H column cell blank when there is no date yet entered?

Thanks!

I'm trying to calculate the number of days a task is overdue. C2 contains the date the assignment is due and d2 is the date the assignment was completed. Example:

Assignment Due Date Assignment Completion Date Days Overdue

5/9/2016 5/2/2016 0

5/10/2016 5/13/2016 3

I need to be able to calculate the number of days overdue. What formula do I use? I know I can't use the DATEIF formula. Thank you for your help.

Can you Please help me out with the following problem.I need to get two values from two different cells and combine it in one cell with certain preexisting characters.

Ex: Cell A1 contains "Aakash" Cell A2 contains "D V". I need final expression in cell A3 as "UI-Aakash D V". Please let me know if it is possible and if it is kindly let me know how.

Hello Aakash,

You can concatenate the values in this way:

="UI-"&A1&" "&A2

Or, use the CONCATENATE function:

=CONCATENATE("UI-",A1," ",A2)

If the second date is empty and I want the value to remain blank or say not complete how do I set that up. I am getting #value

Hi Alex,

You can use the IF function to check for blank cells, like this:

=IF(AND(A1<>"", B1<>""), DATEDIF(A1,B1,"d"), "")

or

=IF(AND(A1<>"", B1<>""), DATEDIF(A1,B1,"d"), "not complete")

I have a spreadsheet that provides the date of when an business item is received for processing. And a second column for a date when the item is submitted for processing. I would like a third column that highlights each item where the difference between receipt and submission is greater than 3 working days. Thank you for your assistance.

Hello Svetlana,

Can you Please help me out with this: I need to calculate number of working years, months and days between two dates with break.

EX: If few employee start working at one date and working certain period then left company and after few years comeback. With DATEDIF function using SUM function getting wrong result.

Please let me know if it is possible and if it is kindly let me know how.

Hello Svetlana,

Please could you assist me in my below calculation because I am struggling 20 hours and cannot get it using many formulas.

I have this formula : Cell D44 03:15:30 (where dd:hh:mm) - Cell D45 00:10:52 (where dd:hh:mm). I want to get the result in the cell D46, in case that D45 is greater then appears ####### I tried many solution but they didn't work.

Additional, when I form the cell D46 with different colour based on the result then when I close the Worksheet and re-open it again the colour hasn't remained as I formed. I don't want to use the form 1904, I used it but the dates were not those I wanted to get. I used Excel 2011 in Mac computer.

Thanks a lot in advance.

Hi,

I am trying to find the chronological age in years and months between a birthday and a testing date.

For example:

Birth Date: 3-3-2001

Test Date: 6-29-2007

Then the age would be: 6 years 3 months

Birth Date: 8-2-1999

Test Date: 5-8-2006

Then the age would be: 7 years 9 months

Birth Date: 11-24-1985

Test Date: 8-13-2006

Then the age would be: 21 years 8 months

Is this possible? Thanks so much!

I am not great at using Excel, so I apologize if this question seems a little basic, but I need help. I have a spreadsheet that has two columns of dates. I would like to use datedif to automatically calculate the difference between these sets of dates into a third column without having to type the formula into each cell. Is there a way to do this?

Hello Torey,

Of course, you don't have to type the formula in each cell manually. Just write the DATEDIF formula for the top cell using relative cell references (without the $ sign, like A1). Then double click a small square at the lower right-hand corner of the formula cell (it will change to the plus sign as soon as you hover over it), and the formula will copy across the entire column.

You can find a handful of other ways to copy formulas in Excel here: How to copy a formula in Excel.

Hi

Svetlana

Thank you, but I do not have good English helped me to formula

=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"

Thank you,Thank you

How to convert "2 years 10 months" into number of days

Hi,

I want to know which formula I have to use if I want to calculate no. of days before a specific date, and total no. of days after or from specific date

for ex: if am calculating number of sick leave days for employee per year and different period of sick leave given.

Regards

Hi

I want to know the formula which will just give year and month where month is not in 2 decimal. Here is the formula which I have used =DATEDIF(E7,TODAY(),"Y")& "."&" "&DATEDIF(E7,TODAY(),"YM")

and result is 0.10 which is 10 months, with this result I am not able to filter the file in chronological order.

Is there a way to resolve this problem.

Thanks, it was really helpful!!!

Hi!

My question: first date = 12.02.2000, second date = 1.5.2012. How to get in excel 2007 or 2013 the number of days of leap years ?

(The number of these days should be 324+366+366+121 = 1177 days).

Thanks to the possible response.

Very good formula, thanks for this

If i give those Date and time, Then what difference between those day's and time it is ?

Give an Example:

Two date with time....

Start date & time: 11:39 PM 12/2/2016

end date & time: 01:39 AM 12/3/2016

Then, How is differentiating two identities in excel.

I want just actual time..........

Please help me........

Hi,

I need this code to exclude duplicates from the previous 10 days inclusive.

=IF(COUNTIFS(A$2:A$20002,A2,B$2:B$20002,B2)>1,"1","")

I'm counting duplicate errors but I takes circa 10 days to close an error so I need to exclude those.

Thanks,

Niall

ABA CHAVHAN Nov-14

ABA CHAVHAN Dec-14

ABA CHAVHAN Jan-15

ABA CHAVHAN Feb-15

ABA CHAVHAN Mar-15

ABA CHAVHAN Apr-15

ABDULRAHIM S KHAN Dec-14

ABDULRAHIM S KHAN Jan-15

ABDULRAHIM S KHAN Feb-15

ABDULRAHIM S KHAN Mar-15

ABDULRAHIM S KHAN Apr-15

ABDULRAHIM S KHAN May-15

AJAY APPA GAIKWAD Nov-14

AJAY APPA GAIKWAD Dec-14

AJAY GAGAT Nov-14

AJAY GAGAT Dec-14

AJAY GAGAT Jan-15

AJAY GAGAT Feb-15

in this case which formula calculating month

How to get the difference between Date which contains time as well. I am looking into find the "Days difference by comparing two cells"

Cell A: 12-13-2016 19:13

Cell B: Current Date

In Column C: I want to know how old is the ticket pending.

Hi,

I'm currently making a company ID with an expiration date. In this case there are 2 basis for expiration date. 1 is passport expiration and the other one is visa expiration. I need to see which of the 2 expires first so that the ID validity will be based on whatever dates comes first. hoping for you response. Thanks!

Hi, I am trying to figure the number of years worked between hire date and the end of our next fiscal year. When I enter the formula it gives me an answer of 1/19/1900. This is the formula I am using =DATEDIF(C2, D2, "Y") C2 is the date of hire and D2 is 06/30/2018. Can someone please help me fix this?

Hi there, really struggling with this...

I am trying to determine the number of nights per week that someone has stayed in accommodation based on their check in (Ci) and check out (Co) dates compared to the week start (Ws) date and week end (We) date.

Brackets are the named columns / rows.

Would really appreciate your help on this as would save a lot of manual entry time!

Many thanks

Matt

D1 to D100 is the due date and I need column E to give the number of says past due date - PLEASE HELP!!

D1 to D100 is the due date and I need column E to give the number of days past due date - PLEASE HELP!!

When I try and perform a sort based on results from this formula, it does not sort correctly. Same thing happens if I use a simple =B2-A2 equation. It looks like Excel is returning a number (as opposed to a date). Any clue why it won't sort? Any ideas for a solution?? :-)

Thanks in advance!!!!

Hi requested support. i want to calculate the number of days after close of month. if their is any support methodology for this query. Please support.

Hello there, I have a question. I have tried to read all posts but it's hard to know which formula I need. I have a birthdate and as of today I can figure out the exact age by year, month and day. Now, I need to know in 3 weeks from now by a specific date how old the pet will be. For instance, the birthday is 11/24/06 so I know from today how old the pet is. But now I need to know for future dates of 02/16/17, 03/15/17 and 04/20/17 exactly how old on those dates the pet will be. I have lined up the dates in the row but I keep getting errors when I try and create a formula from the birthdate...help?

I have just used the DATEDIF formula for Years, Months and Days. I would now like to round these figures up i.e. 14 years 30 Days = 14 years 1 Month/8 years 4 months and 30 days = 8 years and 5 months. Is there an easy way to do that or should I just revert to the DATEDIF formula for Years and Months? Many thanks.

Hi, I would like to know the Beginning Week Number and Ending Week Number of the month. Thank you.

Unsure if your comment is aimed at me however I am using a start date in one column and an end date in another column. Dates starting are usually the 1st of the month and end dates are normally the last day of the month....

Hi Rach,

Try this:

=IF(A1"",DATEDIF(A1,TODAY(),"d"),"")

Hi Deni,

Try this:

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

Hi, good day.

I need help in converting the following if-statements in Excel formula:

1. If A1 and B1(there are formulas in the column that make of them look blank, although the actual cell is not blank) are blank, then C1 is "";

2. If A1 is not blank and B1(there are formulas in the column that make of them look blank, although the actual cell is not blank) is blank, then C1 is "Pending";

3. If A1 and B1 are not blank, then C1 is "Completed".

What formula can I use?

Thank you in advance.

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

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.

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