Get Weekday number based on YYYYMMDD ?

[JumboCactuar]

Hi,

I'm trying to output day number based on date in format YYYYMMDD but unsure how

E.g Monday = 1 to Sunday = 7

20202905 = 5

20202505 = 1

I think I need to convert to a true date first but maybe another way


Thanks

 

[JoeMo]

Another way ....

Book1
	A	B
1	20202905	5
2	20202505	1
Sheet9

Cell Formulas
Range	Formula
B1:B2	B1	=WEEKDAY(DATE(LEFT(A1,4),MID(A1,7,2),MID(A1,5,2)),2)

 

[JumboCactuar]

Thanks @JoeMo

Will it achieve the same result regardless of excel version/language or Windows language.

As my data is always YYYYMMDD

But some users have their date in 29/05/2020 and others in 05/29/2020

 

[JoeMo]

Thanks @JoeMo

Will it achieve the same result regardless of excel version/language or Windows language.

As my data is always YYYYMMDD

But some users have their date in 29/05/2020 and others in 05/29/2020

You are welcome. Despite the title of your post, the data you used in your example are in YYYYDDMM - not YYYYMMDD - format. The solution I posted will work with the YYYYDDMM format in the USA region., and I believe in any Excel version. I don't know about other regions.

 

[Dave Patton]

The input information that you provided and result that you need not clear.
Consider the following.

T202005c.xlsm
	A	B	C	D
11	29-May-20	20200529	5	B11 date custom formatted yyymmdd
12
13	20200529	29-May-20	5	A13 text information
14
3a

Cell Formulas
Range	Formula
B11	B11	=A11
C11,C13	C11	=WEEKDAY(B11,2)
B13	B13	=DATE(LEFT(A13,4),MID(A13,5,2),RIGHT(A13,2))

 

[JumboCactuar]

@JoeMo @Dave Patton

sorry for the confusion, it is YYYYMMDD as title

how would i output column C values with VBA ?

 

[Dave Patton]

What information is in Column A? Is the data Text or Dates custom formatted.
Use the formula cited for the type of input that you have.
You can custom format the date column to show the Day like Monday.

 

[Dave Patton]

T202005c.xlsm
	A	B	C
34	Friday	5	29-May-20
35	Thursday	4	28-May-20
36	Wednesday	3	27-May-20
37	Tuesday	2	26-May-20
38	Monday	1	25-May-20
39
40	20200529	Fri 29-May-20	5
41	20200528	Thu 28-May-20	4
42
3a

Cell Formulas
Range	Formula
C40:C41,B34:B38	B34	=WEEKDAY(A34,2)
C34:C38	C34	=A34
A35:A38	A35	=A34-1
B40:B41	B40	=DATE(LEFT(A40,4),MID(A40,5,2),RIGHT(A40,2))

 

[sandy666]

with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TText = Table.TransformColumnTypes(Source,{{"Raw", type text}}),
    Date = Table.AddColumn(TText, "Date", each Text.End([Raw], 2)&"/"&Text.Middle([Raw], 4, 2)&"/"&Text.Start([Raw] ,4)),
    TypeLocale = Table.TransformColumnTypes(Date, {{"Date", type date}}, "en-GB"),
    DOW = Table.AddColumn(TypeLocale, "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
    TSC = Table.SelectColumns(DOW,{"Date", "Day of Week"}),
    DayName = Table.AddColumn(TSC, "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    DayName