Compare date against multiple dates

[Struggling]

I have a date (say current month) and I want to check to see if that date is
before, after or in the same month as other dates found in other cells.
Once it meets the condition, I want it to return a cell reference related to
the date that meets the condition.

Example: I have the current month in cell AP6. I have dates in cells K8,
AA8, AD8 & AG8. If cell AP6 is less than cell K8, then give me cell L8,
otherwise compare cell AP6 to AA8, if AP6 is less than AA8, then give me AB8,
etc until Excel has checked all of the dates. Also, if AP6 happens to be in
the same month as K8, then I need to prorate L8 based on the number of days
in K8. The same is true if AP6 is in the same month as AA8, AD8 or AG8 then
I need to prorate the corresponding cell. If none of the conditions are
true, then 0 or error is appropriate.

Because I have other things to check, using a nested IF statement doesn't
work. Any ideas on checking these conditions?

Thanks.

 

[robert111]

you could try and compare the "text" value of the month of the date, eg
jan,feb,mar,apr. Make a lookup table where jan=1 feb=2 mar=3


so check if the number returned from the lookup table is <, = or > the
number returned when AP6 is offered to the lookup table