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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,942
Messages
5,514,309
Members
408,995
Latest member
Berville141

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top