Combining 3 formulas (determine wk # max date, wk# min date, combine results)

[willow1985]

Is there a way to combine these 3 formulas?

=MAX(DATE(T2,1,1),DATE(T2,1,1)-WEEKDAY(DATE(T2,1,1),2)+(S2-1)*7+1)
=MIN(DATE(T2+1,1,0),DATE(T2,1,1)-WEEKDAY(DATE(T2,1,1),2)+(S2*7))
=CONCATENATE(TEXT(U2,"[$-409]D-MMM;@")," to ",(TEXT(V2,"[$-409]d-mmm;@")))

(T2 is the year and S2 is the week number)
1st determines the max date in a week number
2nd determines the min date in a week number
3rd combines the results of the first 2 so they give the week number range, example:

Week 38 is: 16-Sep to 22-Sep

Any help would be appreciated.

Thank you

Carla

 

[Eric W]

I suppose you mean something like this:

=CONCATENATE(TEXT(MAX(DATE(T2,1,1),DATE(T2,1,1)-WEEKDAY(DATE(T2,1,1),2)+(S2-1)*7+1),"[$-409]D-MMM;@")," to ",(TEXT(MIN(DATE(T2+1,1,0),DATE(T2,1,1)-WEEKDAY(DATE(T2,1,1),2)+(S2*7)),"[$-409]d-mmm;@")))

just by replacing U2 in the last formula with the first formula, and V2 with the second formula. But honestly, even though it takes 3 cells, your original configuration is easier to read and maintain.

 

[willow1985]

That is true. Thank you for your help

 

[Eric W]

You're welcome.