days of the week in sentence

cortonn

New Member
Joined
Sep 30, 2014
Messages
4
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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum!

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

Excel Workbook
ABCD
1SunFALSEMon, Thu, Sat.
2MonTRUE
3TueFALSE
4WedFALSE
5ThuTRUE
6FriFALSE
7SatTRUE
Sheet1
 

cortonn

New Member
Joined
Sep 30, 2014
Messages
4
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1SunTRUESun and Tue and Sat.
2MonFALSE
3TueTRUE
4WedFALSE
5ThuFALSE
6FriFALSE
7SatTRUE
Sheet1
 

cortonn

New Member
Joined
Sep 30, 2014
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
OK - maybe this then?

p.s. your English is good.

Excel Workbook
LMNOPQRS
1SunMonTueWedThuFriSatResult
2TRUETRUETRUETRUETRUETRUETRUESun, Mon, Tue, Wed, Thu, Fri and Sat.
3TRUEFALSEFALSEFALSEFALSEFALSEFALSESun.
4FALSEFALSEFALSEFALSEFALSEFALSETRUESat.
5TRUEFALSETRUEFALSETRUEFALSETRUESun, Tue, Thu and Sat.
6FALSETRUEFALSETRUEFALSETRUEFALSEMon, Wed and Fri.
7FALSEFALSEFALSEFALSEFALSEFALSEFALSE
Sheet1
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Sep 30, 2014
Messages
4
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,965
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top