Identifying and Applying a formula specific to a month or quarter end

[tthebert]

Hi all,

I am likely not searching in the most efficient way but am not finding the solution to my problem.

I am trying to write a formula that will identify a date, within a list of daily dates (4/2/16, 4/3/16, etc.), that would be the month end (and quarter end) and apply a separate formula to it.

I.e., =IF(9/30/16 is a month end date, 1, 2)

I suppose I could build a table of month and quarter end dates and then say if this value appears in this table, then 1, otherwise 2. Not even sure how that formula would go

Thank you
Troy

 

[iliace]

For end of month, this is relatively easy. You can say, for example, you have a date in A2:

=2-(A2=EOMONTH(A2,0))

returns 1 if A2 is the end of the month, 2 if it isn't. If you prefer the IF form for readability, it would go something like this, slightly longer:

=IF(A2=EOMONTH(A2,0), 1, 2)


For quarter end, this is a little trickier, but assuming that your quarters end in March, June, September, and December:

=2-(A2=EOMONTH(A2,0))*(MOD(MONTH(A2), 3)=0)

Again, IF form is slightly longer,

=IF(AND(A2=EOMONTH(A2,0), MOD(MONTH(A2), 3)=0), 1, 2)


You can use the MOD logic for any of the combination of months 3 months apart. For example, for Jan/Apr/Jul/Oct:

MOD(MONTH(A2)=1)


Hope this makes sense.

 

[tthebert]

This works well, thank you.

Hopefully will also help someone else in the future.