I am trying to build a formula, that will display some text, based on 2 dates. The first date needs to be within the last 10 days of any giving month, and the second date needs to be within the first 10 days of the month. Example:
Date1 = 06/27/11
Date2 = 07/08/11
IF date1 is within 06/20/11 to 06/30/11 AND Date2 is within 07/01/11 to 07/10/11 then the condition is true, if not of course it is false. The range has to adjust based on the month of course as some will have 30/31/28 or 29 days depending on year of course. Is there a forumla to calculate those ranges? I do have other conditions also based on on the dates where it cannot be greater then 90-92 days or just 3 months ago and cannot be less then 30-31 days or just 1 month ago. That part is easy to do with an AND() condition but i am unsure on the range and how to get it to go based on the months accordingly.
Any help would be appreciated.
Last edited by paxile2k; 07-01-2011 at 01:57 PM.
If Date1 is in Cell A1, then this formula will return TRUE if the date is in the last 10 days of the given month:
If Date2 is in Cell A2, then this formula will return TRUE if the date is in the first 10 days of the given month:=(DATE(YEAR(A1),MONTH(A1)+1,0)-A1)<=10
=DAY(A2)<=10
By my reckoning 20th June isn't in the last 10 days of June.
If A2 must be in the first 10 days of any month and A1 in the last 11 days of the previous then try
=AND(DAY(A2)<=10,A1+11-DAY(A1+11)=A2-DAY(A2))
or do you want to specify a particular month and check against that?
Audere est facere
after looking it over i could try this:
B2=06/29/11
C2=0708/11
Formula:
=IF(AND(B2>EOMONTH(NOW(),0)-10,B2<EOMONTH(NOW(),0),C2>DATE(YEAR(C2),MONTH(C2),1),C2<DATE(YEAR(C2),MONTH(C2),10)),"TRUE","FALSE")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks