Date Range Help
Posted by Kevin Merlo on January 21, 2002 5:18 PM
We perform scheduled maintenance intervals 92 days apart. They are called 92, 184, 368 inspections, with the higher inspection being more extensive and covering the requirements of the lesser inspection. Here's the dates when the next inspections are due, in order: (92) 4-6-02, (184) 7-7-02, (368)6-19-02. I know MIN will give me 4-6-02 but I actually need to work the 368, because the time between 4-6-02 and 6-19-02 is less than 92 days. My brain hurts trying to figure out how to make Excel do this calculation for me. I promise to name my next kid after the person that can give me the answer!!!!!
Posted by Barrie Davidson on January 21, 2002 6:51 PM
Kevin, can you explain your selection criteria a little further? For example, when would the 184 days inspection come in to play?
BarrieBarrie Davidson
Posted by Kevin Merlo on January 21, 2002 7:03 PM
Barrie,
I'm trying to have Excel tell me when the next inspection is due. However, if the next inspection due is a 92 day but one of the other higher inspections is due less than 92 days from this date then I need to work the higher inspection. Once I work a 184 then I've done a 184 and a 92 at the same time. If I work a 368 I've covered the 368, 184 and 92 all at the same time. Theoretically these days would always be 92 days apart but for other reasons these dates change. I hope I'm being clear enough and it's very kind of you to help. Thank you!
Posted by Barrie Davidson on January 21, 2002 7:10 PM
Kevin, I think this will work for (assumes 92 date is in cell A1, 184 date is in cell A2, and 368 date is in cell A3):
=MIN(A1+92,A2,A3)
Regards,
Barrie
PS - if your next child is a girl, don't name her Barrie. That's just cruel :)Barrie Davidson
Posted by Aladin Akyurek on January 22, 2002 12:43 AM
Like Barrie, I'll assume the 92, 184, and 368 dates to be in A1:A3 in that order.
Array-enter in some cell:
=MAX((A1:A3)*(ABS((A1:A3-TODAY()))<=92))
You need to hit control+shift+enter at the same time, not just enter, to array-enter a formula.
Aladin
===========
Posted by Kevin Merlo on January 22, 2002 9:00 PM
Thank you very much!!!!