Hello,

Could someone please help me with the following:

I need to run a macro that lets the user know when a date has been on the
spreadsheet for exactly 14-calendar days.

The dates are in Column D of my spreadsheet named: AOG_UNDA Database. The
column header is named: Date Demanded.

77AVMF 0848
04
UNDA
04 Jun 2004
BATTERY STORAGE
66-090-1903
86Z
4
0
BMF

77AVMF 0849
04
UNDA
04 Jun 2004
CASE & VALVE ASSEMBLY
01-428-8889
904-002-786
35
0
OXY

77AVMF 0850
04
UNDA
09 Jun 2004
ELECTRON TUBE
01-269-5704
34-056085-02
1
0
EW



Shown above is a small portion of my spreadsheet. Column AF is the last
column on my spreadsheet.

I need to PrintPreview the output.

The macro needs to run once per day. So when the user opens up the
spreadsheet each day the macro needs to automatically run on start-up.

Any help would be greatly appreciated.

Kind regards,

Chris.

consider using advanced filter.
setup a criteria range:


(are the exported dates really dates or text that represents a date?
if the latter, your filter needs to be a string too..

AOG_UNDA
=today()

if the filter works...


turn on the macro recorder
repeat the steps to filter the data
stop the macro recorder

rename macro1 to auto_open

and you should be done

incase the dates are strings: you'll need to include
following lines in the macro:
Range("a2").numberformat="@"
Range("a2").value=format(date,"dd mmm yyyy")



HTH






keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Chris Hankin" wrote:

Hello,

Could someone please help me with the following:

I need to run a macro that lets the user know when a date has been on
the spreadsheet for exactly 14-calendar days.

The dates are in Column D of my spreadsheet named: AOG_UNDA Database.
The column header is named: Date Demanded.

77AVMF 0848
04
UNDA
04 Jun 2004
BATTERY STORAGE
66-090-1903
86Z
4
0
BMF

77AVMF 0849
04
UNDA
04 Jun 2004
CASE & VALVE ASSEMBLY
01-428-8889
904-002-786
35
0
OXY

77AVMF 0850
04
UNDA
09 Jun 2004
ELECTRON TUBE
01-269-5704
34-056085-02
1
0
EW



Shown above is a small portion of my spreadsheet. Column AF is the
last column on my spreadsheet.

I need to PrintPreview the output.

The macro needs to run once per day. So when the user opens up the
spreadsheet each day the macro needs to automatically run on start-up.

Any help would be greatly appreciated.

Kind regards,

Chris.

Hi Chris,

The following assumes you want to test the dates on the 4th row of each set
of data in your list (i.e. 04 Jun 2004), rather than when those dates were
input.

If so, you don't need a macro for this - you can use conditional formatting
instead. For example, suppose the date you want to test is in A1, you could
insert the following formula as the driver to a conditional format:
=TODAY()-A1=14
and set the cell to have, say, a yellow background when the condition is
true.

You could use a similar formula to drive a text response in another cell.
For example:
=IF(TODAY()-A1=14,"Date has been on the spreadsheet for exactly 14-calendar
days","")

Cheers


"Chris Hankin" wrote in message
...
Hello,

Could someone please help me with the following:

I need to run a macro that lets the user know when a date has been on the
spreadsheet for exactly 14-calendar days.

The dates are in Column D of my spreadsheet named: AOG_UNDA Database. The
column header is named: Date Demanded.

77AVMF 0848
04
UNDA
04 Jun 2004
BATTERY STORAGE
66-090-1903
86Z
4
0
BMF

77AVMF 0849
04
UNDA
04 Jun 2004
CASE & VALVE ASSEMBLY
01-428-8889
904-002-786
35
0
OXY

77AVMF 0850
04
UNDA
09 Jun 2004
ELECTRON TUBE
01-269-5704
34-056085-02
1
0
EW



Shown above is a small portion of my spreadsheet. Column AF is the last
column on my spreadsheet.

I need to PrintPreview the output.

The macro needs to run once per day. So when the user opens up the
spreadsheet each day the macro needs to automatically run on start-up.

Any help would be greatly appreciated.

Kind regards,

Chris.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004

Thanks KeepITcool,

I am a newbie at this type of stuff.

The dates are entered in the spreadsheet as follows:

example: 01 Jun 2004

Column D which has all the dates in it is formatted in dd mmm yyyy
format.

So I am not sure if these dates are text or not.

Please advise on how to setup an advanced filter and criteria range to
accomplish my task as I do not have a clue.

Your help is greatly appreciated.

Kind regards,

Chris.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks macropod,

I do not wish to use conditional formatting because the spreadsheet
contains several thousand line entries. Conditional formatting would
not be very practicle.

The dates in my spreadsheet are located in Column D. They are entered
in by the user as follows:

example: 01 Jun 2004

Column D is formatted: dd mmm yyyy

Please advise on how I can use your formula to print-out a list of
spreadsheet entries that have been in the database for exactly 14-days.

I am a newbie and would appreciate any help.

Thanks,

Chris.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Chris,

Dont expect to get full tutorials in a newsgroup.
You'll get pointers and for specifice questions you'll get solutions.

THEN you use HELP/BOOKS/GOOGLE to find yourself some examples.
Never underestimate what you can learn with HELP and a little
experimenting.

For filtering to work the data MUST have column Headers.
Then you familiarize yourself with Filtering. ...

Start with AUTOFILTER.

select a cell within the db,
Data/Filter/Autofilter.

If you find autofilter acceptable for your needs, you can easily use
macrorecorder. then edit the recorded code to change the hardcoded date
to


Advanced Filter is more powerfull, BUT it requires ou to set up Criteria
Range. (it allows you to hide unmatching records, or export matching
records to a new location)


You can filter on multiple criteria
You can filter on dates that fall within a range.


A simple example:

row1 DATE
row2 =Today()-14

row4 column headers for data
row5 first data row

Data/Filter/AdvancedFilter
data range: a4:f99?
crit range: a1:a2


again: Help has good examples on criteria ranges
(maybe best NOT to start with dates but start with some simple
selections on ID. When you understand the principle and you see how it
works....

THEN continue on the dates.
For filtering on dates the format of the date is unimportant.
What is important is to know if a date is a date or a text string.

Try =d1+1 if that gives you a date= then excel has stored them as
date serial numbers.

if it gives an error... then you've imported text.
and you'll probably want to convert the test to a date serial.

again read help on date conversion.





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Chris Hankin wrote:


Thanks KeepITcool,

I am a newbie at this type of stuff.

The dates are entered in the spreadsheet as follows:

example: 01 Jun 2004

Column D which has all the dates in it is formatted in dd mmm yyyy
format.

So I am not sure if these dates are text or not.

Please advise on how to setup an advanced filter and criteria range to
accomplish my task as I do not have a clue.

Your help is greatly appreciated.

Kind regards,

Chris.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Chris Hankin,
You can find some tutorials on advanced filter at
http://www.contextures.com/xladvfilter01.html
HTH
Cecil

"Chris Hankin" wrote in message
...
Hello,

Could someone please help me with the following:

I need to run a macro that lets the user know when a date has been on the
spreadsheet for exactly 14-calendar days.

The dates are in Column D of my spreadsheet named: AOG_UNDA Database. The
column header is named: Date Demanded.

77AVMF 0848
04
UNDA
04 Jun 2004
BATTERY STORAGE
66-090-1903
86Z
4
0
BMF

77AVMF 0849
04
UNDA
04 Jun 2004
CASE & VALVE ASSEMBLY
01-428-8889
904-002-786
35
0
OXY

77AVMF 0850
04
UNDA
09 Jun 2004
ELECTRON TUBE
01-269-5704
34-056085-02
1
0
EW



Shown above is a small portion of my spreadsheet. Column AF is the last
column on my spreadsheet.

I need to PrintPreview the output.

The macro needs to run once per day. So when the user opens up the
spreadsheet each day the macro needs to automatically run on start-up.

Any help would be greatly appreciated.

Kind regards,

Chris.

Thankyou Cecilkumara for the link to the tutorial pages.

Kind regards,

Chris.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks KeepITcool for your advice. I'll check out the Help\Books\Google
pages.

Kind regards,

Chris.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!