Date Range Output.

Hi, I'm trying to create a formula using IF, AND, OR, however if VBA is required please advise.

So I have 4 date ranges as below;
01/07/10 - 30/06/11 - Year 1
01/07/11 - 30/06/12 - Year 2
01/07/12 - 30/06/13 - Year 3
01/07/13 - 30/06/14 - Year 4

If I enter in a Cell A1 '20/10/12' then Cell A2 will say "Year 3"..... and so on.

I had a guess at this, but no luck
=IF(AND(OR(H12>1/7/2009,H12<30/7/2010,"Year 1",OR(H12>1/7/2010,H12<30/7/2011,"Year 2",OR(H12>1/7/2011,H12<30/7/2012,"Year 3",OR(H12>1/7/2013,H12<30/7/2014,"Year 4"))))))

Appreciate any Help.

Alternatively If I don't have the end date, can the formula work by just using the Start Date as a triggers? So every time a date passes the threshold in moves to the next year. e.g. Year 1, Year 2.

Many Thanks

Pls find the file attach, I am givin you example with 3 formulas in your case, and just assumed about your lay out table. 2 regular formula anda 1 array formula

cheers

Thank you Azumi for your input and assistance.

However I'm not totally sure that the formulas you supplied works. I put in 20th March in various years and all the formula outputs went blank? Please advise?

Also is it possible to code the date ranges into the actual formula instead of a VLookup table?

I have reattached our spreadsheet so you can see.

Thank youExample-Azz.xlsx

You have your sample workbook?, maybe date formatted could be a problem, to upload click "Go Advanced" button and find paperclip button to attach your file.

Originally Posted by mtilbury

Thank you Azumi for your input and assistance.

However I'm not totally sure that the formulas you supplied works. I put in 20th March in various years and all the formula outputs went blank? Please advise?

Also is it possible to code the date ranges into the actual formula instead of a VLookup table?

I have reattached our spreadsheet so you can see.

Thank youAttachment 361472

Your sample file Post #3 has dates listed as text in B1:B4 as well as A7. When I re-entered those as numeric dates on your sample workbook the formulas return values. I put another formula (array entered with Ctrl + Shift + Enter) in F8 that puts upper and lower limits on the valid date inputs in A7.

LOOKUP date-range-output-example-azz & djw V2.xlsx

Under testing these formulas are not working for me. I am testing multiply dates to test the formula and they are either coming up blank for #Value?

I'm based in the UK. but don't think that setting would affect the formula?

I have attached.

Ensure that all your dates in column A are proper Excel dates, then use this formula in B8:

=IFERROR(VLOOKUP(A7,A1:C4,3),"")

The formula in B13 is an array formula, so needs to be committed using the key combination of Ctrl-Shift-Enter (CSE). If you do this correctly then Excel will display curly brackets { } around the formula when viewed in the formula bar, though you do not type these yourself.

Hope this helps.

Pete

Thank you - It works

Appreciate everyones input.