Convert date into Month and Year to use > <

MaddieG - Aug 23, 2017 at 10:55 AM
 MQ - Aug 25, 2017 at 08:50 AM

I have two sets of dates that I need to compare using <,>,=. One of my dates is formatted mm/dd/yyyy. the other date is mm/yyyy.

The goal is for both sets of numbers to include ONLY mm/yyyy. not only in the way they are displayed to the user but in terms of actual value.

my goal is to show that something occurred in the month it was intended to happen
(mm/dd/yyyy = mm/yyyy for example 01/15/2016=01/2016 as january 15th occured in the month of january)

however using the traditional text option (= TEXT(A2,"mm/yyyy) doesn't work as they are TEXT fields and i cant get the operations >,<,= to work and if i just change the formatting the original date is retained and so in the above example it would show that 01/15/2016= 01/2016 is FALSE.

any ideas?

ive been working on this for too long!


1 response

Look into the function DATEDIF, it may do what you need.

=DATEDIF(startdate, endate,"M")

would return the number of complete months between the two dates.
Hey xx,

thanks for the suggestion. unfortunately no dice.

of the 2 dates i have, in E2 and K2, it is possible that either E2 or K2 is earlier than the other. so if i use DATEDIF(E2,K2,M) it returns a value if the dates are exactly the same (0) or if K2>E2, but in cases where E2>K2 it returns the #NUM! error.

This would be okay as then i would know all #NUM! error really mean that E2>K2 but it is giving the same error if E2>K2 and is in the same month as K2. so 10/04/2016>10/01/2016 when i would need those to show as 0.

ive found a only slightly tedious work around, im just going to update every date within the month to the 1st of the month for both data sets and then i can use my original plan.