Conditional formula's in excel-calculating with dates / age andaffiliation date calculus

[Saar]

Good afternoon,

I would need a hand on the following 3 things I'm trying to do in Excel 2010.

1. If a date falls between two others return TRUE
e.g. Date in Service < Date of 25th Birthday < Date of 6 Months in Service
Date in service in B6
Date of 25th Birthday in B12
Date of 6 Months in Service in B11
My dates are formated as dd/mm/yyyy
I wrote the formula in B19 =B6<B12<B11 which is not doing the trick.
Any suggestions?

2. Employees can subscribe to a service depending on their age and time in service.
I'm stuck on: Affiliation from the 1st of the month following or coinciding with the completion of 6 months of service
or if age 25 is reached before the 6 months of in service, affiliation from the 1st of the month following the 25th Birthday.

For the Affiliation from the 1st of the month following or coinciding with the completion of 6 months of service I wrote this formula in cell B27 =DATE(YEAR(B6);MONTH(B6)+1; 1) with B6 being Date in service

How do I combine it with the second condition: if age 25 (B12) is reached before the 6 months of in service (B11), affiliation from the 1st of the month following the 25th Birthday

3. How to write the formula which returns the affiliation date for: affiliation from the 1st of the month following or coinciding with the date in service (B6) if this one was on the first of the month ?

Thanks in advance for your help!

 

[joeu2004]

1. If a date falls between two others return TRUE
e.g. Date in Service < Date of 25th Birthday < Date of 6 Months in Service
Date in service in B6
Date of 25th Birthday in B12

If B5 contains the date of birth, B12 could be: =EDATE(B5,25*12)


Note: In my region, we use comma as a list separator. It appears that
you might use semicolon. Translate accordingly. (I would make too many
mistakes.)

Date of 6 Months in Service in B11

B11 should be: =EDATE(B6,6)

My dates are formated as dd/mm/yyyy

The format per se does not matter. The question is: does Excel recognize
input as legitimate dates?

To be sure, verify that =ISNUMBER(B6) returns TRUE; likewise for B11 and
B12.

I wrote the formula in B19 =B6<B12<B11 which is not doing the trick.
Any suggestions?

Although that is a reasonable mathematical expression, Excel does not
recognize it as you intended. Instead, Excel parses that as (B6<B12)<B11,
which translates in TRUE<B11 or FALSE<B11, depending on whether or not
B6<B12 is TRUE.

You should write:

=AND(B6<B12,B12<B11)


The descriptions of #2 and #3 seem a little too complex to try without
testing. I will address them separately, if no one else does.

 

[joeu2004]

Date in service in B6
Date of 25th Birthday in B12
Date of 6 Months in Service in B11
My dates are formated as dd/mm/yyyy [....]
2. Employees can subscribe to a service depending on their age
and time in service.
I'm stuck on: Affiliation from the 1st of the month following
or coinciding with the completion of 6 months of service
or if age 25 is reached before the 6 months of in service,
affiliation from the 1st of the month following the 25th Birthday.

For the Affiliation from the 1st of the month following or
coinciding with the completion of 6 months of service I wrote this
formula in cell B27 =DATE(YEAR(B6);MONTH(B6)+1; 1) [....]
How do I combine it with the second condition: if age 25 (B12) is
reached before the 6 months of in service (B11), affiliation from
the 1st of the month following the 25th Birthday

(Note: Recall that I use comma as a list separator; you use semicolon. I
would make too many mistakes I tried to make the adjustment.)

=IF(B12<B11, DATE(YEAR(B6),MONTH(B6)+(DAY(B6)>1),1),
DATE(YEAR(B12),MONTH(B12)+(DAY(B12)>1),1))

I assume you meant to write "from the 1st of the month following
__or_coinciding_with__ the 1st of the month following the 25th birthday" in
the latter case.

The conditional expression +(DAY(B6)>1) returns 1 if TRUE, 0 if FALSE.
Similarly for +(DAY(B12)>1).

3. How to write the formula which returns the affiliation date
for: affiliation from the 1st of the month following or coinciding
with the date in service (B6) if this one was on the first of the month ?

I believe that is covered by my formula for #2.

Your original formula did not cover the "or coinciding" condition. That is
why I added the conditional expression +(DAY(B6)>1) and similarly for B12.

If my formula for #2 does not cover #3, please provide a concrete example so
that I can better understand how #3 is distinguished.

 

[Saar]

Hi,

Thank you so much for your help. It did the tricks and now I have learned how to compile future formula's

Kind regards,
Saar