How to insert Date to spreadsheet?

[sezuh]

HI,
i'll be greatful if anyone would help me to insert insert date for the whole year In column "A" and how to format Sat. with green,Sun. with Red colour all across the sheet.Thank you as ever for all help in advance.
Sheet3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 138px"><COL style="WIDTH: 65px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 14pt">DATES</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="COLOR: #008080">SAT 01 JAN 2011</TD><TD style="COLOR: #008080"> </TD><TD style="COLOR: #008080"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="COLOR: #ff0000">SUN 02 JAN 2011</TD><TD style="COLOR: #ff0000"> </TD><TD style="COLOR: #ff0000"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>MON 03 JAN 2011</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>TUE 04 JAN 2011</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>WED 05 JAN 2011</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>THU 06 JAN 2011</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>FRI 07 JAN 2011</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="COLOR: #008080">SAT 08 JAN 2011</TD><TD style="COLOR: #008080"> </TD><TD style="COLOR: #008080"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="COLOR: #ff0000">SUN 09 JAN 2011 </TD><TD style="COLOR: #ff0000"> </TD><TD style="COLOR: #ff0000"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>MON 10 JAN 2011</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>^</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>^</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>^</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>up to</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="COLOR: #008080">SAT 31 DEC 2011</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>


KIND REGARDS
SEZUH

 

[jeffreybrown]

How about this macro...

Sub InsertDate()
    Dim Rng As Range
    Const NumFormat As String = "ddd dd mmm yyyy"
    Const Fm1$ = "=TEXT(A1,""ddd"")=""Sat"""
    Const Fm2$ = "=TEXT(A1,""ddd"")=""Sun"""
    Set Rng = Range("A1")
    Rng.Value = ("1/1/2011")
    Rng.NumberFormat = NumFormat
    Rng.AutoFill Destination:=Range("A1:A365"), Type:=xlFillDefault
    Columns(1).AutoFit
    With Range("A1:A365")
        With .FormatConditions
            .Delete
            With .Add(Type:=xlExpression, Formula1:=Fm1)
                .Font.ColorIndex = 4    ' <-- Change to suit
            End With
            With .Add(Type:=xlExpression, Formula1:=Fm2)
                .Font.ColorIndex = 3    ' <-- Change to suit
            End With
        End With
    End With
End Sub

 

[MrBill11]

Maybe this will work for you.
I entered 1/1/2011 in A1 and Saturday in B1 then created 2 conditional format rules B1 cell value = "Sunday" RED, and another B1 cell value = "Saturday" GREEN. Then highlighted both cells & copied it down.

 

[GK039]

Try in cell A1:

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(ROW())+27),"yyyy mmmm dd dddd")

It will give you today's date in YYYY MMMM DD DDDD format. Note that plus 27 is used to give you the current date

Copy from A1 down as long as you like

Then with A1 be the active cell select all range from A1 downwards - Then go to Conditional Format and where formula is place the following formula:

=OR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))="Saturday",RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))="Sunday")

Choose a format that you like and it will color the dates that are Saturday or Sunday

I hope that helps you if you want to avoid VBA

George

 

[GK039]

A correction to my proposal. You want different colors for Sundays and Saturdays, So in conditional format use half of the above formula for Sunday and half for Saturday.

That is:

RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))="Saturday"

and choose green color

RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))="Sunday"

and choose red color

George

 

[sezuh]

HI Jeff,
thanks for the code,Absolutly perfect... much obliged...I realy appreciate your effort and help.
Have Good night.
sezuh

 

[jeffreybrown]

Hi George,

You are right, a macro may not be the way to go for the OP, but why such a complex formula and conditional formatting?

In A1 you can enter 1/1/2011 with a custom format of ddd dd mmm yyyy then drag down 365 rows.

Highlight range and with conditional formatting add two rules:
=Text(A1,"ddd")="Sat" >> pick font
=Text(A1,"ddd")="Sun" >> pick font

 

[GK039]

Hi Jeff,

Well you re right ) much simpler way there. Thanks for the tip

George

Hi George,

You are right, a macro may not be the way to go for the OP, but why such a complex formula and conditional formatting?

In A1 you can enter 1/1/2011 with a custom format of ddd dd mmm yyyy then drag down 365 rows.

Highlight range and with conditional formatting add two rules:
=Text(A1,"ddd")="Sat" >> pick font
=Text(A1,"ddd")="Sun" >> pick font

 

[jeffreybrown]

No problem George...I thought I may have missed something, but in the end learning along with you

 

[sezuh]

Hi Mrbill11 and George, thanks for your response its amazing when several people offer thier help ,thats what i like about this forum.Your help much appreciated......
George if i do not want to start from today but from any earlier date what change to be done to excel formula?
sezuh