Excel Date/Day

[Steve Voltage]

i would like to extract the day from the date in my spreadsheet.

i.e GB / Leic 1st Jun / 14:15 5f Mdn Stks

anyone know how? i can use this formula =TEXT(WEEKDAY(A1), "ddd") to give a number for a day i.e saturday=1 but i need the 1st of june bit from spreadsheet race/date info.

[silver]

The Get_Word function here -

http://www.ozgrid.com/VBA/extract-words-function.htm

- should be useful.

It will allow you to extract the fourth and fifth blocks of text from the cell contents that you mention in your post.

You'll extract them separately then you'll need to join them together.

[Steve Voltage]

The Get_Word function here -

http://www.ozgrid.com/VBA/extract-words-function.htm

- should be useful.

It will allow you to extract the fourth and fifth blocks of text from the cell contents that you mention in your post.

You'll extract them separately then you'll need to join them together.

Thanks silver

[osknows]

Hi Arnold,

Put this code in a MODULE

Code: Select all

Function extractdate(strdate As String)
Dim strarray() As String
Dim intday As Integer

'today's date and day
datenow = DateValue(Now())

'date in string
strarray = Split(strdate, " ")
intday = Replace(Replace(Replace(Replace(LCase(strarray(3)), "st", "", _
vbTextCompare), "nd", "", vbTextCompare), "rd", "", _
vbTextCompare), "th", "", vbTextCompare) * 1

'calculate year variance
Do Until day(datenow) = intday
datenow = datenow + 1
Loop

',7 makes Saturday 1
extractdate = Weekday(CDate(intday & " " & strarray(4) & " " & year(datenow)),7)

End Function


then if your string is in cell A1, in excel use

=extractdate(A1)