Formula Help

[xgunda420x]

Hi,

I have a excel spreadsheet and on the first column I have my customer
and on the second column I have TIER 1, 2, 3, 4, and I have their Vali
From and Valid To date on. Because of the system I am using all m
dates are not flowing. Is there a formula which would tell me tha
this customer’s date is not flowing and I could just go back in ther
and fix it. If someone knows can you please please help me. I woul
be more than happy to send out the visual.

Thank you

+-------------------------------------------------------------------
|Filename: example.doc
|Download: http://www.excelforum.com/attachment.php?postid=3623
+-------------------------------------------------------------------

 

[Fable]

Hello!

If by “Flowing” you mean that the End Year does not match (that’s the
only thing I could see different), placing the formula below into your
Conditional Format (Column B) will high light those dates/rows for you.


=IF(TEXT(A1,"YYYY")<>TEXT(B1,"YYYY"),1,0)

Note: In the Conditional Formatting change the back ground color to a
yellow or red.

Hope this helps!

Fable

 

[xgunda420x]

Hi Fable,

Thank you so much for the response. This formula is great. This
formula was another thing that I researching for.

But the flow that I wanted to do is as follows.

For example, let say T1 Valid from and T1 Valid to date is from
10/01/2004 to 05/31/2005, T2 Valid from and T2 Valid to date is from
10/01/2004 to 09/30/2007, I want formula which will tell me that T2
Valid from and T2 Valid to has wrong date or give me a return value
which will tell me that this row has wrong date so I could get estimate
# of how many dates I have to fix. I have this example in Row 17.

This is what I mean by flow, if I have T1 Valid to date 05/31/05 then I
want T2 Valid from start date as of 06/01/05 so it will go by order.

Thank you.

 

[xgunda420x]

Can someone please help me?

Above is all the information that I need

 

[PokerZan]

I want formula which will tell me that T2 Valid from and T2 Valid to has
wrong date or give me a return value which will tell me that this row
has wrong date so I could get estimate # of how many dates I have to
fix.

This is the problem, and probably why you aren't getting a response.

How are you (or we) going to determine what is a "wrong" date? Are
there set rules for what constitutes a "valid" date? Is the "Valid to"
date a set number of years/months/days from the "Valid from" date? If
you can provide a bit more information as to what would make a date
"wrong" or "right" it would prolly hasten your answer/help.

PZan

 

[xgunda420x]

Thank you for your honest response.

Wrong date meaning would be something overlapping or gap on the dates.
Right date meaning everything smooths, for example, 10/01/2004 t
02/28/2005, 03/01/2005 to 09/30/2007. If you know any formula pleas
let me know.

Thank you so much again

 

[PokerZan]

We still need to know what the "rules" are for determining a "wrong
date.

By there very nature, there is a gap between two dates. Withou
knowing how you would determine a wrong date, I can't come up with
formula that would for you

 

[Fable]

Hi xgunda420x ,


Sorry my first attempt did not help, but I agree with PokerZan we nee
a “Criteria” to be able to help out. The explanation you just gave doe
not really tell us what the conditions are to ID the “problem”. Yo
would have to tell us something like e.g. “if T1 Valid from is greate
than T2 Valid from, that is a bad date” or maybe some like “if th
number of days of T1 Valid versus T2 Valid is greater than 365 days
then that is a bad date.”. That type of explanation would help us hel
you.

Hope we can help you .

 

[xgunda420x]

I am not exactly sure how to put this but here it goes.

The rule would be very simple not to overlap or have gap on any dates.
For example, if the date is 10/01/2004(T1 Valid from) to 02/28/2005 (T
Valid to) and 04/01/2005 (T2 Valid from) to 09/30/2007 (T2 Valid to).
This is a gap and I want formula to give me True or false. Anothe
example if the date is 10/01/2004(T1 Valid from) to 02/28/2005 (T
Valid to) and 02/01/2005 (T2 Valid from) to 09/30/2007 (T2 Valid to).
This is overlapping example, I want formula to populate this and giv
me either True or false. Because when get all the false I have to g
back to the system and fix all of the dates. I

In another word T1 Valid to date should not be greater than T2 Vali
from date and T2 valid to date should not be greater than T3 Valid fro
date and T3 valid to date should not be greater than T4 valid to date.

I know it is really complicated. I hope this helps

 

[PokerZan]

Ok, I think I have this figured out.

You will need a help column for each date "gap" you are looking to
measure. The first one will be (based on your example) in column K
starting with cell K2. Here is the formula for you.

=IF((C2-D2)>1,"GAP",IF((C2-D2)<1,"OVERLAP",""))

This will put "GAP" where there is a gap of more than one day between
the "T1 Valid to" and the "T2 Valid From" fields, as well as return an
"OVERLAP" if the difference in days between the two dates are less than
1 day.

Just copy this column down the page and it should tell you where and
how you need to fix the data.

In Column L you would just change the "(C2-D2)" to "(E2-F2)".

Hope this helps.

PZan

 

[xgunda420x]

Ok, I think I have this figured out.

You will need a help column for each date "gap" you are looking to
measure. The first one will be (based on your example) in column K
starting with cell K2. Here is the formula for you.

=IF((C2-D2)>1,"GAP",IF((C2-D2)<1,"OVERLAP",""))

This will put "GAP" where there is a gap of more than one day between
the "T1 Valid to" and the "T2 Valid From" fields, as well as return an
"OVERLAP" if the difference in days between the two dates are less than
1 day.

Just copy this column down the page and it should tell you where and
how you need to fix the data.

In Column L you would just change the "(C2-D2)" to "(E2-F2)".

Hope this helps.

PZan

Is there anyway we could use one formula and get the same info for T1,
T2, T3, T4? I tried above formula, which is good.

Thank you so much.

 

[PokerZan]

Is there anyway we could use one formula and get the same info for T1
T2, T3, T4?

I don't know of a way to do this in less than one column per time spa
measured, if anyone else wants to chime in be my guest.

PZa