Combine day name and monthly date in the same cell

[vladimiratanasiu]

Hello!

I have a table including days of the week and their annual equivalent date, that can be accessed by the link pontaj.xlsx . I need to combine automatically in the same cell the name of each day with its real annual date, but keeping only the first one / two letter(s) of that day and its equivalent date from the month, as is shown in the column E from the table. My question is whether I could apply a special format, a formula etc., in order to get the necessary structure of the content. Thank you!

 

[fadee2]

hi vladimiratanasiu
there are couple of solutions with same results
solution 1:

=TEXT(C2,"dd")&"-"&TEXT(C2,"ddd")

Solution 2:

=C2

then format resulting cells by going to Custom Category and using "dd-ddd" (without quotes) as type.

both will result in values like

Book1
	A	B	C	D	E	F	G
1	ish days of the week	Romanian equivalent name	Date	English final formatted date	Romanian final formatted date
2	Sunday	Luni	22/11/20	22-Sun	22-L	22-Sun	22-Sun
3	Monday	Marți	23/11/20	23-Mo	23-M	23-Mon	23-Mon
4	Tuesday	Miercuri	24/11/20	24-Tue	24-Mi	24-Tue	24-Tue
5	Wednesday	Joi	11/25/2020	25-We	25-J
6	Thursday	Vineri	11/26/2020	26-Th	26-V
7	Friday	Sâmbătă	11/27/2020	27-Fr	27-S
8	Saturday	Duminică	11/28/2020	28-Sat	28-D
9	…...	…....	…...	…...	…....
10	Tuesday	Marți	12/01/20	01-Tue	01-M
Sheet6

Cell Formulas
Range	Formula
F2:F4	F2	=TEXT(C2,"dd")&"-"&TEXT(C2,"ddd")
G2:G4	G2	=C2

 

[vladimiratanasiu]

hi vladimiratanasiu
there are couple of solutions with same results
solution 1:

=TEXT(C2,"dd")&"-"&TEXT(C2,"ddd")

Solution 2:

=C2

then format resulting cells by going to Custom Category and using "dd-ddd" (without quotes) as type.
View attachment 26700

 

[vladimiratanasiu]

hi vladimiratanasiu
there are couple of solutions with same results
solution 1:

=TEXT(C2,"dd")&"-"&TEXT(C2,"ddd")

Solution 2:

=C2

then format resulting cells by going to Custom Category and using "dd-ddd" (without quotes) as type.
View attachment 26700

both will result in values like

Book1
	A	B	C	D	E	F	G
1	ish days of the week	Romanian equivalent name	Date	English final formatted date	Romanian final formatted date
2	Sunday	Luni	22/11/20	22-Sun	22-L	22-Sun	22-Sun
3	Monday	Marți	23/11/20	23-Mo	23-M	23-Mon	23-Mon
4	Tuesday	Miercuri	24/11/20	24-Tue	24-Mi	24-Tue	24-Tue
5	Wednesday	Joi	11/25/2020	25-We	25-J
6	Thursday	Vineri	11/26/2020	26-Th	26-V
7	Friday	Sâmbătă	11/27/2020	27-Fr	27-S
8	Saturday	Duminică	11/28/2020	28-Sat	28-D
9	…...	…....	…...	…...	…....
10	Tuesday	Marți	12/01/20	01-Tue	01-M
Sheet6

Cell Formulas
Range	Formula
F2:F4	F2	=TEXT(C2,"dd")&"-"&TEXT(C2,"ddd")
G2:G4	G2	=C2

Thank you very much!

 

[fadee2]

Ypu are very welcome... ???

 

[vladimiratanasiu]

Ypu are very welcome... ???

Hello!

I'm getting back with an additional question to your answer posted yesterday for me. I wish to format the date so that the name of day may be reduced only to the first two letters and may start with uppercase, as shown in the attachment. Is any method to do that? Thank you in advance.

 

[fadee2]

=TEXT(C2,"dd")&"-"&Left(TEXT(C2,"ddd"),2)

 

[vladimiratanasiu]

=TEXT(C2,"dd")&"-"&Left(TEXT(C2,"ddd"),2)

Basically, the sollution is ok, but the uppercase issue is not covered. The Excel sets up automatically the special format indicated by you, and in Romanian language the day's name starts with lowercase. As a result, if I select the Romanian language the name is by default written with lowercase. If you could help me to solve this problem too, I will be very thankful to you.

 

[fadee2]

try
=TEXT(C2,"dd")&"-"&proper(left(TEXT(C2,"ddd"),2))

 

[vladimiratanasiu]

try
=TEXT(C2,"dd")&"-"&proper(left(TEXT(C2,"ddd"),2))

Thank you a lot, it works very well!