How do i please add seasons to a reference?

Hi

I work in the Travel industry and i pay Tour Operators.

At the moment one of my operators lets say My Travel sends in a statement with a supplier ref on it but my branch inputs the supplier reference with a season for example

Statement Ref 123456
Branch Ref 123456/S07

Now what i need to do is seasons are dependant on dep date i need to write a formula that does the following

Say Season S07 is dep date from 1/5/6 to 1/10/6. I need to write a sum that looks in a dep date column looks at the date and if the date falls between the two from and to dates then add "S07" I would also repeat this process for W07 etc etc.

Any ideas please?

Regards

Clint

Hi Clint,

A sumproduct() formula can handle that ...
What are your specifics, in terms of ranges. etc ... ?

Carim

Thanks for reply.

I dont know the specifics in regards to season dates i will have to contact operator or find out officially when Summer and Winter start etc.

At the moment we use a system called TCAT or Travelcat and we have the ability to upload excel/txt files and do a matching process on TCAT against what the supplier (MY Travel) has given us in this case a statement.

At the moment it wont match because the branch is adding seasons on our TCAT system when the supplier (My Travel) is not adding seasons on typically a statement would like this.

Abta No Lead Name Dep Date Gross Supplier Ref

A1926 Smith 12/07/07 500 123456

However the branch would enter the reference as say 123456/S07

Instead of going into TCAT and taking out supplier ref i want to add it to existing spreadsheet with a formula of sorts that takes the supplier reference and looks at a combination of dep dates.

Lets say S07 = Dep dates from 1/5/7 to 31/10/7
Lets say W07 = Dep dates from 1/11/7 to 30/4/8
Lets say S08 = Dep Dates from 1/5/8 to 31/10/8

Etc etc.

Thanks

Hi,

Sorry I did not express myself properly ...
I meant within your worksheet, in which cells, columns , ranges
your data appear ...
in order to give you a complete formula ...

HTH
Carim

Hi Clint,

If your branch ref is located in column B, your amounts in column C
and data goes from row 2 to row 20,
formula would be :

Please Login or Register  to view this content.

HTH
Carim