Calculate number of days depending on some date criteria

**rickyilas** · 05-18-2012, 06:40 PM

Hi Everyone,

I need to have a formula in Column D that will calculate the Total EOT (Extension of time Days) depending on the person's mob/demob date. If he is present within the EOT period, he will get EOT days. I have attached a file that describes different criteria.

Thanks for your help.

**Cutter** · 05-18-2012, 07:32 PM

Try this in D5 and dragged down:

=MAX(IF(AND(B5<$H$3,C5>$I$3),$J$3,IF(AND(B5>=$H$3,C5<=$I$3),C5-B5,IF(B5>=$H$3,MIN($I$3,C5)-B5,C5-MAX($H$3,B5)))),0)

**xladept** · 05-18-2012, 08:52 PM

Hi rickyilas,

Try this:

Please Login or Register  to view this content.

**rickyilas** · 05-23-2012, 07:07 PM

Thanks to both of you....both formula works for me.

**Cutter** · 05-23-2012, 07:19 PM

You're welcome and thanks for the 'star tap' but I hope you went with xladept's solution. It was what I was trying for but it just wasn't coming to me so I settled for what I posted.