Using DatedIf for a MM YY calculation difference

[RedMisterExcel]

Hey guys, so I’ve been trying to set up a cell to calculate the amount of months between a start and end date. The problem is, I need to set the dates up so that they are in MM YY format, and when i do that, DatedIf doesn’t seem capable of calculating. Anyone know how to fix this or if it’s just impossible?

 

[RedMisterExcel]

You are welcome.

It makes more sense when you understand how dates are actually stored in Excel. They are actually stored as numbers, specifically the number of DAYS since 1/0/1900.
You can easily see this if you enter any valid date in Excel, and then change the format of the cell to "General". You will then see the date as Excel sees it.
So in order to store it as a date (which really stores it as a number), you need a Day component to the date to it knows which number to set it to.

The other option would be to enter it as text, and add a date conversion to your formula.

How do you do the date conversion of text?

 

[Joe4]

So if your date entry in cells B17 and C17 is always a text entry like "09 10" where the first two digits are month, and the last two digits are year, you can use the DATE function to convert it to a date.
Note the format of the DATE function is:
DATE(year, month, day)

So we can get the year by starting with a string of "20" and adding the 2 right-most characters from the cell.
We can get the month by taking the two left-most characters from the cell.
And we can use "1" for our day.

So to convert the entry in cell B17 would look like this:

=DATE("20" & RIGHT(B17,2),LEFT(B17,2),1)

So to put it all together in our DATEDIF forumula, we would have this:

=DATEDIF(DATE("20" & RIGHT(B17,2),LEFT(B17,2),1),DATE("20" & RIGHT(C17,2),LEFT(C17,2),1),"m")

 

[RedMisterExcel]

So if your date entry in cells B17 and C17 is always a text entry like "09 10" where the first two digits are month, and the last two digits are year, you can use the DATE function to convert it to a date.
Note the format of the DATE function is:
DATE(year, month, day)

So we can get the year by starting with a string of "20" and adding the 2 right-most characters from the cell.
We can get the month by taking the two left-most characters from the cell.
And we can use "1" for our day.

So to convert the entry in cell B17 would look like this:

=DATE("20" & RIGHT(B17,2),LEFT(B17,2),1)

So to put it all together in our DATEDIF forumula, we would have this:

=DATEDIF(DATE("20" & RIGHT(B17,2),LEFT(B17,2),1),DATE("20" & RIGHT(C17,2),LEFT(C17,2),1),"m")

Interesting, thank you. Will it overcomplicate my sheet though? In the sense that say I want to move the location of the cells sometime down the line. Would it be better to stick with the m/d/y format?