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?

 

[bebo021999]

With start date and end date are REAL date, but formatted "MM YY", DATEDIFF still works .

 

[SMK041]

Just playing around, I custom formatted a couple of cells to mm yy and it's still working for me.

=datedif(start_date,end_date,"m)

Can you upload an example?

 

[RedMisterExcel]

N/A

 

[RedMisterExcel]

Just playing around, I custom formatted a couple of cells to mm yy and it's still working for me.

=datedif(start_date,end_date,"m)

Can you upload an example?

Whoops sorry, put B17 twice. HEre you go.

 

[Joe4]

As was mentioned in the first reply, in order to use DATEDIF, you need REAL dates entered.
You do not have that. As least one of your entries is Text.

As was mentioned, after you enter a real date (real dates require year, month, and day), you can format to show it in "MM YY" format, to display it like that.

If you will always have Text entries, you will need to then convert the values to real dates before you can use the DATEDIF function on them.

 

[RedMisterExcel]

As was mentioned in the first reply, in order to use DATEDIF, you need REAL dates entered.
You do not have that. As least one of your entries is Text.

As was mentioned, after you enter a real date (real dates require year, month, and day), you can format to show it in "MM YY" format, to display it like that.

If you will always have Text entries, you will need to then convert the values to real dates before you can use the DATEDIF function on them.

Ah copy, thank you, but here's my thing, for the purposes of this form, I will not know what the days are, only the month/year. Therefore, even if I wanted to, I could not put in a day. Does this mean I can never use DatedIf?

 

[Joe4]

Ah copy, thank you, but here's my thing, for the purposes of this form, I will not know what the days are, only the month/year. Therefore, even if I wanted to, I could not put in a day. Does this mean I can never use DatedIf?

Sure you can.
Since Day is not important to your calculation, simply use "1". Every month has a "1st" in it.

 

[RedMisterExcel]

Sure you can.
Since Day is not important to your calculation, simply use "1". Every month has a "1st" in it.

Copy, thank you. Not an ideal solution (would like to just put MM/YY), but you gotta make lemons with lemonade I guess. Solved.

 

[Joe4]

Copy, thank you. Not an ideal solution (would like to just put MM/YY), but you gotta make lemons with lemonade I guess. Solved.

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.