insert a range of date

L

Lynn

Hi,
i need to insert a date range into excel in the following format, where
the first date is today's date and the 2nd date is 4 days later.

Example:
28/11/2005 - 02/12/2005

How can I achieve the above format so that the month will also be
updated according to calendar.

thanks
 
N

Niek Otten

Wit the start date in A1, this in A2 or B1:
=A1+4
Copy down (or right) as far as you need.
Excel will take care of the correct month and year
 
N

Niek Otten

Format>Cells>Number, choose Date and select one of the examples.
If none fits your requirements:
Format>Cells>Number, choose Custom. In the Type box, enter: dd mmm yyyy
 
N

Niek Otten

Did you try my suggestion? If so, what was it that made you ask for a macro
instead?
 
L

Lynn

i tried
Worksheets("Sheet1").Rows(3).NumberFormat = "dd mmm yyyy"
Worksheets("Sheet1").Cells(3, 11).Value = "(" & Date & ")"

but i find that the result is mm/dd/yyyy

what could be wrong?
 
N

Niek Otten

Yes. By using "(" you make it a text entry. That will override any
formatting and insert a US style date.

Leave out the test or use

Worksheets("Sheet1").Cells(3, 11).Value = "(" & format(Date,"dd mmm yyyy") &
")"

But you should realise that this last option makes it text so you can't use
it as a date for any further calculation
 
N

Niek Otten

<Leave out the test or use>

Leave out the text....

as you hopefully guessed already
 
Top