days of the week in sentence

[cortonn]

Hi all.

I have 7 checkbox, one for each day of week.

I have 7 cells with true or false (vinculated from checkbox).

I need to concatenate in a sentence selected days of the week (text), but considering the commas and final dot.

Can I do it with one or more functions?

Thanks!

 

[FormR]

Welcome to the forum!

If I have understood correctly, here is one way that may help:

Excel Workbook
	A	B	C	D
1	Sun	FALSE		Mon, Thu, Sat.
2	Mon	TRUE
3	Tue	FALSE
4	Wed	FALSE
5	Thu	TRUE
6	Fri	FALSE
7	Sat	TRUE
Sheet1

 

[cortonn]

Yes! But.......one but, before the last day, if it´s more than one, I want to add "and" instead of comma. This is the problem that I have.

By the way, I have only the "true" and "false" values, only, ​​in a row, not a column, from the check boxes marked, not from other cells. It is a problem?

 

[FormR]

It is a problem?

No, no problem - hopefully you are able to adapt this to your actual set-up - but if not what cells contain the TRUE/FALSE and what days do they relate to?

Excel Workbook
	A	B	C	D
1	Sun	TRUE		Sun and Tue and Sat.
2	Mon	FALSE
3	Tue	TRUE
4	Wed	FALSE
5	Thu	FALSE
6	Fri	FALSE
7	Sat	TRUE
Sheet1

 

[cortonn]

Uhm... well....

I don't know how to "paste" the data from my file, but it's something like:

https://www.dropbox.com/s/nsod44z1q6c0n8u/01.JPG?dl=0


What I need to do is a little more complicated, something like "Monday, Tuesday and Saturday." or "Wednesday." where there is only one or "Monday, Tuesday, Thursday and Sunday."

Not all with commas or all with "and".

PD: Thanks, especially, to understand my English xD

 

[FormR]

OK - maybe this then?

p.s. your English is good.

Excel Workbook
	L	M	N	O	P	Q	R	S
1	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Result
2	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	Sun, Mon, Tue, Wed, Thu, Fri and Sat.
3	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	Sun.
4	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	Sat.
5	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	Sun, Tue, Thu and Sat.
6	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	Mon, Wed and Fri.
7	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Sheet1

 

[Rick Rothstein]

Give this formula a try...

=TRIM(SUBSTITUTE(MID(IF(B1,", "&A1,"")&IF(B2,", "&A2,"")&IF(B3,", "&A3,"")&IF(B4,", "&A4,"")&IF(B5,", "&A5,"")&
IF(B6,", "&A6,"")&IF(B7,", "&A7,""),2,99)&" ",","," and",MAX(1,COUNTIF(B:B,TRUE)-1)))&IF(COUNTIF(B:B,TRUE),".","")

 

[cortonn]

Thanks both !! Thank you very much.

Both solutions work very well. I adapted the functions to Spanish and cells, and they are what I needed.

Although, to use the second, I need to have a range with the names of the days.

A beer for both!