View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Ron de Bruin Ron de Bruin is offline
external usenet poster
 
Posts: 11,123
Default Auto-entering the start and end of a month

Try this

1 + 2 ) Use Now like this

If cell b1 is empty it use the system date
If not the it use the date in b1

=DATE(YEAR(IF(B1="",NOW(),B1)),MONTH(IF(B1="",NOW( ),B1)),1)

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Laphan" wrote in message ...
Dear Ron

Many thanks for the info. This was perfect.

Only thing that has confused me is how do I do the following:

1) In the example '=DATE(YEAR(A1),MONTH(A1),1)', its stating that I've
entered a date in A1. How can I get it to look at the system date. For
example, if the worksheet automatically grabbed today's date (11-11-03) then
it could automatically get the first and last dates.

2) I know I want the above to grab the date automatically in an 'onLoad'
state you might call it, but I also want to be able to allow the user to
change these values should they need to.

To give you background info, this Excel worksheet will go and extract info
from an SQL DB within the date range that these 2 fields are going to
specify. The Dutch user should run this report at the end of each month,
but in an ideal world this can't always be the case so I want to be able to
let them overwrite these dates with whatever date they want.

Rgds

Laphan


Ron de Bruin wrote in message
...
Check out Chip's site Laphan
http://www.cpearson.com/excel/datetime.htm#DaysInMonth

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Laphan" wrote in message
...
Hi All

Can anybody give me pointers on how I can get 1 specific cell to
auto-display the start of the current month, Laphan 1-11-03 and another

cell
to show the end, eg 30-11-03. I also want these cells to be editable so
that the user can change the date if they want to something like 20-12-03.

Any ideas??

Rgds


Lagan