day in a week

A

Andy B

Hi

If you mean Thursday, as opposed to 4th day of week, one way is to type the
date into a cell and change the cell format to ddd
 
B

Bob Phillips

If you want to leave the original cell as is, add =TEXT(A1,"ddd") to B1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

JE McGimpsey

Alternatively, avoid a function call by entering =A1 in cell B1, then
formatting the cell as "ddd".
 
B

Bob Phillips

Good point!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

alekm

Ok, it helped.
Let me ask additionally how can I get that day order number (day in a week) for date.
Also is there any way I can fill column with all dates for eg May 20004.
 
J

JE McGimpsey

Check out the WEEKDAY() function.

For the dates, enter the start date in the first cell, click on the fill
handle (lower right corner of the cell) and drag down 30 cells.
 
B

Bob Phillips

Format the call as 'd' or put =TEXT(A1,"d") in B1

Sub SetDates()
Dim StartDate

StartDate = CDate(InputBox("Please supply a start date, format
dd/mm/yyyy"))
Range("A1").Value = StartDate
Cells(2, 1).FormulaR1C1 =
"=IF(MONTH(R1C1)=MONTH(R1C1+ROW()-1),R[-1]C+1,"""")"
Range("A2").AutoFill Destination:=Range("A2:A31"), Type:=xlFillDefault
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
N

Norman Harker

Hi Alekm!

Re: is there any way I can fill column with all dates for eg May 20004

Here's a formula way:

A1:
Put a base date (eg 1-May-2004)

B1:
=DATE(YEAR($A$1),MONTH($A$1),1)
B2:B32
=IF(B1="","",IF(B1=DATE(YEAR($A$1),MONTH($A$1)+1,0),"",B1+1))
 
Top