Formula Help

**xgunda420x** · 07-25-2005, 01:41 PM

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 Valid From and Valid To date on. Because of the system I am using all my dates are not flowing. Is there a formula which would tell me that this customer’s date is not flowing and I could just go back in there and fix it. If someone knows can you please please help me. I would be more than happy to send out the visual.

Thank you.

**Fable** · 07-25-2005, 02:15 PM

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** · 07-26-2005, 10:58 AM

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** · 07-27-2005, 10:34 AM

Can someone please help me?

Above is all the information that I need.

**PokerZan** · 07-27-2005, 11:29 AM

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** · 07-27-2005, 01:31 PM

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 to 02/28/2005, 03/01/2005 to 09/30/2007. If you know any formula please let me know.

Thank you so much again.

**PokerZan** · 07-27-2005, 01:40 PM

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. Without knowing how you would determine a wrong date, I can't come up with a formula that would for you.

**Fable** · 07-27-2005, 02:27 PM

Hi xgunda420x ,

Sorry my first attempt did not help, but I agree with PokerZan we need a “Criteria” to be able to help out. The explanation you just gave does not really tell us what the conditions are to ID the “problem”. You would have to tell us something like e.g. “if T1 Valid from is greater than T2 Valid from, that is a bad date” or maybe some like “if the 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 help you.

Hope we can help you . .

**xgunda420x** · 07-27-2005, 02:39 PM

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 (T1 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. Another example if the date is 10/01/2004(T1 Valid from) to 02/28/2005 (T1 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 give me either True or false. Because when get all the false I have to go back to the system and fix all of the dates. I

In another word T1 Valid to date should not be greater than T2 Valid from date and T2 valid to date should not be greater than T3 Valid from 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** · 07-27-2005, 03:52 PM

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** · 07-27-2005, 04:28 PM

Originally Posted by 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

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.