CODEWITHSUNDEEP
excelexcel-formula
fabio ractz
fabio ractz

Reputation: 11

Show date range for a week in excel

I have the following code:

=UPPER(TEXT(DATE(K2;K1;1+14) + CHOOSE(WEEKDAY(DATE(K2;K1;1)); 1;0;6;5;4;3;2);" \s\e\m\a\n\a d \d\e mmmm \d\e aaaa"))

Which displays:

SEMANA DE 15 DE SETEMBRO DE 2014

But I need to display:

SEMANA DE **15-21** DE SETEMBRO DE 2014

What am I missing?

Upvotes: 1

Views: 243

Answers (2)

barry houdini
barry houdini

Reputation: 46331

This is an alternative approach you could try - should get you the same result

="SEMANA "&22-WEEKDAY(DATE(K2;K1;6))&"-"&28-WEEKDAY(DATE(K2;K1;6))&" DE "&UPPER(TEXT(K1*29;"mmmm"))&" DE "&K2

Upvotes: 1

Axel Richter
Axel Richter

Reputation: 61860

=SUBSTITUTE(UPPER(TEXT(DATE(K2;K1;1+14) + CHOOSE(WEEKDAY(DATE(K2;K1;1)); 1;0;6;5;4;3;2);" \s\e\m\a\n\a d-""$EOW$"" \d\e mmmm \d\e aaaa"));"$EOW$";DAY(DATE(K2;K1;1+14) + CHOOSE(WEEKDAY(DATE(K2;K1;1)); 1;0;6;5;4;3;2))+6)

I have assumed, that you calculate the first Monday after the 15th of the month and want to show this day and 6 days after. The idea is to substitute "$EOW$" in the format-string with the day+6.

Upvotes: 2

Related Questions