convert day abbreviation (Mon,tue,wed.....) To date using Week Number

[Luke1690]

hi,

I have two lots of data week number in column (A) and the Day in column D shown as Mon, Tues, wed, Thu and Fri.
is there an easy way to convert the Day to a date using the Week number and the abbreviation of the Day?

Thanks In advance

 

[lrobbo314]

Seems to work for the current year.

Excel
	A	B	C
7	WeekNum	DOW	Date
8	30	Wed	7/24/2024
Sheet1

Cell Formulas
Range	Formula
C8	C8	=DATE(2024,1,1)+((A8-1)*7)+XMATCH(B8,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-2

 

[felixstraube]

How about:

Freq.xlsx
	A	B	C	D
1	Original day	WeekNum	Day	Calculated Day
2	01/06/2024	22	Sat	01/06/2024
3	09/06/2024	24	Sun	09/06/2024
4	17/06/2024	25	Mon	17/06/2024
5	25/06/2024	26	Tue	25/06/2024
6	03/07/2024	27	Wed	03/07/2024
7	11/07/2024	28	Thu	11/07/2024
8	19/07/2024	29	Fri	19/07/2024
9	27/07/2024	30	Sat	27/07/2024
10	04/08/2024	32	Sun	04/08/2024
Sheet8

Cell Formulas
Range	Formula
B2:B10	B2	=WEEKNUM(A2)
C2:C10	C2	=TEXT(A2, "ddd")
D2:D10	D2	=LET(sy, SEQUENCE(8,, DATE(2024,1,1)), wn, WEEKNUM(sy), startOfWeeksCount, SUM(sy*(wn=2)*(WEEKDAY(sy)=1))-7, days, {"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat"}, daysToAdd, MATCH(C2, days, 0)-1, startOfWeeksCount + (B2-1)*7 + daysToAdd )
A3:A10	A3	=A2+8

Im assuming that it is for the year 2024. You could change the value of 2024 in the formula to anything you like. YEAR(TODAY()) or something.

 

[Luke1690]

Seems to work for the current year.

Excel
	A	B	C
7	WeekNum	DOW	Date
8	30	Wed	7/24/2024
Sheet1

Cell Formulas
Range	Formula
C8	C8	=DATE(2024,1,1)+((A8-1)*7)+XMATCH(B8,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-2

This is exactly what i required thank you for your help and speedy response

 

[lrobbo314]

Awesome! Happy to help.