Auto-entering the start and end of a month

  • Thread starter Thread starter Laphan
  • Start date Start date
L

Laphan

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
 
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


Check out Chip's site Laphan
http://www.cpearson.com/excel/datetime.htm#DaysInMonth
 
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)
 
Many thanks Ron

You're a star!!!!

Rgds

Laphan

PS: noticed you're from the Netherlands - have you ever had to do any
IntraStat reporting in Excel?


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)
 
Back
Top