Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
If i have two dates : 3/15/2021 and 6/22/2020, I just want to compare their value by Month date MMDD. This means i just compare 3/15 and 6/22 . Therefore i can get 6/22 is greater than 3/15 using a if statement.
I tried if( date( '3/15/2021', MMDD') <= date('6/22/2020','MMDD') , Y,N) , system return N which is wrong because 6/22 is greater than 3/15 and should return Y . Can anyone help me out?
I'm not quite sure what you're trying to do - it sounds like you're just trying to compare the day of month, perhaps? If(Day(Date1) <= Day(Date2),'Y','N') in that case.
Edit:
Re-reading, I think you're just trying to remove the year aspect from the date, in which case the above wouldn't be correct, obviously. Instead:
If(MakeDate(2004,Month(Date1),Day(Date1))<=MakeDate(2004,Month(Date2),Day(Date2),'Y','N')
Note that the 2004 is arbitrary and any year figure could be used, but it should be a leap year to ensure February 29th exists.
if( Month( '3/15/2021') <= Month('6/22/2020') , 'Y','N')
If you want to compare on date level you can use DayNumberOfYear() for your comparisons. This function will return the day number of the year for the given date, a number between 1 and 366.
If( DayNumberOfYear('3/15/2021') <= DayNumberOfYear('6/22/2021' ), 'Y', 'N')
I avoided that one because it can be tricky with leap years (February 29th is the same day of the year as March 1st in a non-leap-year I believe, but it's a smaller date)
=if(num#(date(date#( '3/15/2021', 'MM/DD/YYYY'),'MMDD'))<= num#(date(date#('6/22/2020','MM/DD/YYYY'),'MMDD')) , 'Y','N')
try this i got the required o/p as Y. Let me know your requirement meet
@Or : you´re right . Handling leap years can be troublesome, depending on how you wamt to do you analysis.
For just the DayNumberOfYear() function March 1 will allways be 61, and February 29 will always be 60. In a non leap year the DayNumberOfYear() will skip from 59 to 61 in the switch between February and March.