Inserting days in Excel

[gmoorthy]

Is there an easy to insert calendar days in excel it should look like this

mm/dd/yyyy format

1/1/1995
1/2/1995

and go all the way to 12/31/2000

 

[mintjulep]

Fill | Series...

 

[gmoorthy]

Thanks a lot but i have another issue

in the column A have it as

mm/dd/yyyy and in column B i want to say insert into table name ( "date") where date is from column A in the format yyyymmdd in excel when i do a concatenate it gives me a number for the date

 

[mintjulep]

="Date " & TEXT(A1,"yyyymmdd")

 

[randy700]

Not entirely sure I understand what you want, but,

Cell A1: 01/01/2013
Format: mm/dd/yyyy

Cell B1: 20130101
Formula: =A1
Format: yyyymmdd


Randy

 

[Andrzejek]

I believe gmoorthy is trying to create INSERT statements that would look like:
[tt]
INSERT INTO SomeTable (“Date”) VALUES (#1/1/2000#)
INSERT INTO SomeTable (“Date”) VALUES (#1/2/2000#)
INSERT INTO SomeTable (“Date”) VALUES (#1/3/2000#)
INSERT INTO SomeTable (“Date”) VALUES (#1/4/2000#)
...
[/tt]
and the date would from Excel's column A

Have fun.

---- Andy

 

[Andrzejek]

...and if I am correct in my assumption, why not a simple Macro in VBA:

Option Explicit

Sub InsertDates()
Dim datStart As Date
Dim datStop As Date

datStart = CDate("[blue]1/1/2000[/blue]")
datStop = CDate("[blue]12/31/2005[/blue]")

Do While datStart < (datStop + 1)
    Debug.Print "INSERT INTO SomeTable (MyDate) VALUES (#" & datStart & "#)"
    datStart = datStart + 1
Loop

End Sub

I know this is NOT a VBA forum, but why not take an easy way….?

Have fun.

---- Andy

 

[randy700]

Andy,

Your code doesn't provide the text format (yyyymmdd) asked for.
How about this?

A1: 01/01/2013 - format mm/dd/yyyy
B1: =A1 - format yyyymmdd, results in 20130101
C1: =CONCATENATE("INSERT INTO SomeTable (MyDate) VALUES (", B1, ")"


Randy

 

[Andrzejek]

Yes, I know. But I work under certain assumptions, one of which is: gmoorthy wants to create a series of INSERT statements (I guess to Access table) where MyDate field is defined as Date.

I may have wrong assumptions, it wouldn’t be the first time.


Have fun.

---- Andy