insert a range of date

[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

 

[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

 

[Niek Otten]

Another option: Edit>Fill>Series, choose date, day and step value 4

 

[Lynn]

how can i convert 02/12/2005 to 02 Dec 2005 ?

 

[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

 

[Lynn]

is there any macro to convert 02/12/2005 to 02 Dec 2005 ?

 

[Niek Otten]

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

 

[Lynn]

yes, i want to automate this process

 

[Niek Otten]

Sub Macro1()
Selection.NumberFormat = "dd mmm yyyy"
End Sub

If you're new to VBA, look here first:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

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

 

[Lynn]

any clues?

 

[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

 

[Niek Otten]

<Leave out the test or use>

Leave out the text....

as you hopefully guessed already