VBA code for Date ranges

[Guest]

I have 12 cycles based on date range - Cycle 1: July 3 - Aug 3, Cycle 2: Aug
4 - Sep 7,..., Cycle 12: June 25 - June 29. The cycle does not have a fixed
no of days.

Help me how to program it such everytime I click a button, Excel will tag
the corresponding cycle.

Vlookup does not work since there are days not present in my cycles. I don't
want to list down all 365 days and assign cycle no because it defeats the
whole purpose of programming.

Thanks

 

[Carim]

Hi Rook,

Each date can be tested against the beginning and ending dates of your
12 cycles, an UDF can handle these 12 cases ...
Could you list all your cycles ?

Cheers
Carim

 

[Guest]

Hi Carim,

Cycles as follows:

July 3 - Aug 3 Cyc 1
Aug 4 - Sep 4 Cyc 2
Sep 7 - OCt 4 Cyc 3
Oct 5 - Nov 6 Cyc 4
Nov 7 - Dec 7 Cyc 5
Dec 8 - Jan 16 Cyc 6
Jan 17 - Feb 15 Cyc 7
Feb 16 - Mar 19 Cyc 8
Mar 20 - Apr 20 Cyc 9
Apr 23 - MAy 23 Cyc 10
May 24 - Jun 25 Cyc 11
Jun 26 - Jun 29 Cyc 12

Thanks for your help.

Rook

 

[Carim]

Rook,

Thanks for the Cycles ...

It seems Vlookup Fuction can work as follows :

=VLOOKUP(A1,F1:H12,3)

provided, it is built with an initial cell which refers to A1

with your table, say in range F1:H12, cell F1 would equal

=DATE(YEAR($A$1),7,3)

and all other cells dependent ...i.e +31, +1, +31, etc ...

HTH
Cheers
Carim

 

[Bob Phillips]

VLOOKUP can work if you test for no match and action accordingly

=IF(ISNA(lookup_formula),"",lookup_formula)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)