Formula to extract month and day and convert to month abbreviation and week number

[wmichael]

Hello.

Seeking a formula to extract details from the CURRENT DATE START (E) cell to populate in the MONTH (A) cell and the WEEK (B) cell instead of me manually entering the month and week- appreciate your help in advance. Thank you.

--- Michael

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

A	B	C	D	E	F	G	H
MONTH	WEEK	PAST DATE START	PAST DATE END	CURRENT DATE START	CURRENT DATE END	FUTURE DATE START	FUTURE DATE END
Oct	4			Sun, Oct 1, 2017	Mon, Oct 30, 2017
Oct	4			Sun, Oct 1, 2017	Mon, Oct 30, 2017
Jan	4			Sun, Jan 1, 2017	Tue, Jan 30, 2018
Jan	4			Sun, Jan 1, 2017	Tue, Jan 30, 2018
Sep	1			Fri, Sep 1, 2017	Sun, Sep 30, 2018
Jul	2			Sun, Jul 1, 2018

<tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Rick Rothstein]

Re: Seeking formula to extract month and day and convert to month abbreviation and week number

Here more examples with the TEXT function:

	A	B	C	D
1	MONTH	MONTH	WEEK DAY	WEEK DAY
2	abr	abril	qui	quinta-feira
3	mai	maio	sáb	sábado
4	jun	junho	seg	segunda-feira
5	jul	julho	qua	quarta-feira
6	ago	agosto	sex	sexta-feira
7	set	setembro	dom	domingo
8
****	********	**********	**********	**************

<tbody>
</tbody>

Markmzz

Just out of curiosity, are the month and day names usually written in all lower case in your locale (not just in Excel, but in normal writing, such as a newspaper for example) ?

 

[markmzz]

Re: Seeking formula to extract month and day and convert to month abbreviation and week number

Just out of curiosity, are the month and day names usually written in all lower case in your locale (not just in Excel, but in normal writing, such as a newspaper for example) ?

Sometimes yes and sometimes no. Here is one for a Newspaper - Terça-feira, 20 de março de 2018

​

Markmzz​

 

[Rick Rothstein]

Re: Seeking formula to extract month and day and convert to month abbreviation and week number

Sometimes yes and sometimes no. Here is one for a Newspaper - Terça-feira, 20 de março de 2018

​

Markmzz​

I guess that is why Microsoft decided not to make the output for month (or day) names proper case for your locale... still, it might be nice if the documentation mentioned that there is a discrepancy on this depending on locale.

 

[markmzz]

Re: Seeking formula to extract month and day and convert to month abbreviation and week number

I guess that is why Microsoft decided not to make the output for month (or day) names proper case for your locale... still, it might be nice if the documentation mentioned that there is a discrepancy on this depending on locale.

I think that you are right. Now look at the Excel custom lists:

	A	B	C	D	E	F	G	H	I
1	MONTH	MONTH	MONTH	MONTH	WEEK DAY	WEEK DAY	WEEK DAY	WEEK DAY
2	Jan	Janeiro	jan	janeiro	Qui	Quarta-feira	qui	quinta-feira
3	Fev	Fevereiro	fev	fevereiro	Sex	Quinta-feira	sex	sexta-feira
4	Mar	Março	mar	março	Sáb	Sexta-feira	sáb	sábado
5	Abr	Abril	abr	abril	Dom	Sábado	dom	domingo
6	Mai	Maio	mai	maio	Seg	Domingo	seg	segunda-feira
7	Jun	Junho	jun	junho	Ter	Segunda-feira	ter	terça-feira
8
****	********	**********	**********	**************	**********	**************	**********	**************	***

<tbody>
</tbody>

Markmzz

 

[wmichael]

Re: Seeking formula to extract month and day and convert to month abbreviation and week number

You are all awesome!