Joker sign

Dunky13

New Member
Joined
Jul 28, 2010
Messages
6
Hello,
i got here a peace of mine code:
If date2 >= DateSerial(2010, 1, 1) And date2 <= DateSerial(2010, 3, 31) Then

this should check if the data is equal to Q1
but it is only for 2010 but i need from 2000 tot later (lets say 2100)
but i dont want to copy and paste because my total program has already over 1500 lines

so i would like something like this:
If date2 >= DateSerial(2***, 1, 1) And date2 <= DateSerial(2***, 3, 31) Then

but the
sign doesn't work as Joker Sign
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

Is it?

Code:
If date2 >= DateSerial(Year(date2), 1, 1) And date2 <= DateSerial(Year(date2), 3, 31)

Or you could just check the Month of date2.
 

PatrickO

New Member
Joined
Jul 6, 2010
Messages
36
Is your end goal just to determine if the date is within the first quarter, regardless of which year it is in?
 

Dunky13

New Member
Joined
Jul 28, 2010
Messages
6

ADVERTISEMENT

sheet1:
data | data | data | 1/1/2010
data | data | data | 1/3/2010
data | data | data | 15/3/2010
data | data | data | 5/4/2010
data | data | data | 20/5/2010
data | data | data | 30/7/2010
data | data | data | 1/9/2010
data | data | data | 1/2/2011
data | data | data | 27/4/2011

^
|

Sort by date

convert to:
Q1 2010 | data | data | data |
| data | data | data |
| data | data | data |

Q2 2010 | data | data | data |
| data | data | data |

Q3 2010 | data | data | data |
| data | data | data |

Q1 2011 | data | data | data |

Q2 2011 | data | data | data |


^
|
it wil be sorted nice under each other
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
You could use a Pivot Table grouped by Years and Qtrs, which could turn this
Code:
   A        
 1 Date     
 2 1/3/2000 
 3 7/3/2000 
 4 1/3/2001 
 5 7/3/2001 
 6 1/3/2002 
 7 7/3/2002 
 8 1/3/2003 
 9 7/3/2003 
10 1/3/2004 
11 7/3/2004 
12 1/3/2005 
13 7/3/2005 
14 1/3/2006 
15 7/3/2006 
16 1/3/2007 
17 7/3/2007 
18 1/3/2008 
19 7/3/2008 
20 1/3/2009 
21 7/3/2009 
22 1/3/2010 
23 7/3/2010 
Sheet2
[Table-It] version 09 by Erik Van Geit
into this
Code:
   C           D    E         
 4 Sum of Date                
 5 Years       Date Total     
 6 2000        Qtr1 1/3/2000  
 7             Qtr3 7/3/2000  
 8 2001        Qtr1 1/3/2001  
 9             Qtr3 7/3/2001  
10 2002        Qtr1 1/3/2002  
11             Qtr3 7/3/2002  
12 2003        Qtr1 1/3/2003  
13             Qtr3 7/3/2003  
14 2004        Qtr1 1/3/2004  
15             Qtr3 7/3/2004  
16 2005        Qtr1 1/3/2005  
17             Qtr3 7/3/2005  
18 2006        Qtr1 1/3/2006  
19             Qtr3 7/3/2006  
20 2007        Qtr1 1/3/2007  
21             Qtr3 7/3/2007  
22 2008        Qtr1 1/3/2008  
23             Qtr3 7/3/2008  
24 2009        Qtr1 1/3/2009  
25             Qtr3 7/3/2009  
26 2010        Qtr1 1/3/2010  
27             Qtr3 7/3/2010  
Sheet2
[Table-It] version 09 by Erik Van Geit
This is one possiblity. You can play around and get the format and results you need
 

Dunky13

New Member
Joined
Jul 28, 2010
Messages
6

ADVERTISEMENT

sheet1:
data | data | data | 1/1/2010
data | data | data | 1/3/2010
data | data | data | 15/3/2010
data | data | data | 5/4/2010
data | data | data | 20/5/2010
data | data | data | 30/7/2010
data | data | data | 1/9/2010
data | data | data | 1/2/2011
data | data | data | 27/4/2011

^
|

Sort by date

convert to:
Q1 2010 | data | data | data |
| data | data | data |
| data | data | data |

Q2 2010 | data | data | data |
| data | data | data |

Q3 2010 | data | data | data |
| data | data | data |

Q1 2011 | data | data | data |

Q2 2011 | data | data | data |


^
|
it wil be sorted nice under each other
 

Watch MrExcel Video

Forum statistics

Threads
1,109,258
Messages
5,527,659
Members
409,781
Latest member
Maxcwy2020

This Week's Hot Topics

Top