How to Calculate DATEDIF formula in Excel

0 votes

I would like to get the CalculatedValue for the formula =DATEDIF("5/7/2018","6/6/2018","m").In Excel it returns zero.

I would like to know what process used while calculating the above formula.

Can anyone please let me know the actual process behind getting the month between two dates ?

Mar 24 in Database by Edureka
• 9,320 points
38 views

1 answer to this question.

0 votes

Simply divide one date by the other. For example, if cell A2 contains an invoice date of 1/1/2015 and cell B2 contains a paid date of 1/30/2015, you may use the formula =B2-A2 to calculate the number of days between the two dates, which is 29. Excel may format the cell containing the formula as a date, which means that instead of 29, you'll see 1/29/1900. To make the result appear as 29, simply change the formatting for that cell to General or any number format (right-click > Formatting).

Things get difficult if you want to know how many months or years there are between two dates. The most efficient method is to use the DATEDIF() function. This one isn't in the function wizard, so you'll have to remember how to write it in manually. It isn't a typical Excel function, according to the explanation I heard, and it is solely included for backward compatibility with Lotus 1-2-3.

The function's format is DATEDIF (start date,end date,unit).

The start and end dates are self-evident. The unit of measure, which can be "D", "M", or "Y" for days, months, or years, can be "D", "M", or "Y" for days, months, or years, respectively. There are three more unique alternatives, which are explained in the following example.

Sample formulas for each of the unit possibilities are shown in the table below. Column D shows the formula that was used to produce the result given in column C in column C. It's worth noting that the answer only displays whole units. Even if the two dates are a little more than 5 years apart, the calculation on row 4 displays 5 years. The function is not circular. The answer would still be if the two dates were 5 years and 364 days aparrt 

A B C D E
1 START DATE END DATE DIFFERENCE FORMULA UNIT – DIFFERENCE CALCULATED IN..
2 01/01/2010 02/18/2015 1874 =DATEDIF(A2,B2,”D”) Days (gives same result as =B2-A2)
3 61 =DATEDIF(A2,B2,”M”) Months
4 5 =DATEDIF(A2,B2,”Y”) Years
5 17 =DATEDIF(A2,B2,”MD”) Days, ignoring month and year (days from 1st to 18th)
6 1 =DATEDIF(A2,B2,”YM”) months, ignoring year (months from Jan to Feb)
7 48 =DATEDIF(A2,B2,”YD”) days, ignoring year (days from 1/1 to 2/18)
answered Mar 25 by Edureka
• 8,820 points

Related Questions In Database

0 votes
1 answer

How to calculate student pass percentage in excel

Percentage formula in Excel In cell D2, type ...READ MORE

answered Apr 6 in Database by Edureka
• 8,820 points
10 views
0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
34 views
0 votes
1 answer

how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
139 views
0 votes
1 answer

How to calculate age (in years) based on Date of Birth and getDate()

Following can be a solution to your ...READ MORE

answered Feb 23 in Database by Vaani
• 5,040 points
35 views
0 votes
1 answer
0 votes
1 answer

Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

answered Mar 15 in Database by Edureka
• 8,820 points
50 views
0 votes
1 answer

Remove duplicates within a row

To check for duplicates, choose the cells ...READ MORE

answered Mar 15 in Database by Edureka
• 8,820 points
32 views
0 votes
1 answer

Excel Remove Decimal Places

1) E2 is the cell from which ...READ MORE

answered Mar 24 in Database by Edureka
• 8,820 points
43 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP