formula to write the dates of rest days for every employee in one cell

Hello Everybody,
Question: I want a formula to write the dates of rest days for every employee
Note 1 "R" refers to rest days
Note 2 "Row B" contains dates
Note 3 The result are like this "01+07+13+19+20+26"
Office:2007
Limitations:Formula Only-No helper columns-No vba-No powerquery
I hope you help me

Here's an alternative that works for up to 14 Rest days

In AD3 copied down

=SUBSTITUTE(TEXT(SUMPRODUCT(IFERROR(AGGREGATE(15,6,B$2:AC$2/(B3:AC3="R"),{1,2,3,4,5,6,7}),0),100^{6,5,4,3,2,1,0}),"00+00+00+00+00+00+00+")&TEXT(SUMPRODUCT(IFERROR(AGGREGATE(15,6,B$2:AC$2/(B3:AC3="R"),{8,9,10,11,12,13,14}),0),100^{6,5,4,3,2,1,0}),"00+00+00+00+00+00+00"),"+00","")

see attached

.... perhaps changing the range ???? B$2:AC$2 to B$2:AF$2 for 31 columns

I changed the range as above and works perfectly OK placing the formula anywhere..

Re you last post: AGGREGATE is not in Excel 2007.

Why this obsession with everything being short: if there had been a way to make the AGGREGATE function shorter then DDL would have done so ( I only replicated the code). ? If you cannot have more than 14 rest days (50%!) in any month the DLLS's first formula will suffice.

You do not like "helper" rows/columns nor VBA and now you complain about "long" formulae.

If you have Office 2007 at work then this should be the one in your profile: people waste their valuable time on solutions which may not be viable at work.

AGGREGATE is (sometimes) a substitute for INDEX/SMALL array formula but assuming these can be applied you are likely to have an even more complex formula.

Leprince, you do come up with some very challenging problems. A friendly word of advice though. You often bring new requirements along AFTER members have spent a lot of time trying to help you.

Can I suggest that you make it clear IN YOUR FIRST POST, what Excel version you want it to work for, whether VBA is acceptable or not, whether helper columns can be used or not, whether Power Query can be used or not, and so on.

Otherwise you may find a smaller and smaller number of members who are prepared to help you - the remainder being totally frustrated by your introduction of additional restrictions on the sort of solution that you want; AFTER they have FREELY given some of their OWN TIME to you to help.

No.... apart from the obvious question.

Why do you want a very complicated formula to concatenate the dates in this thread and another very complicated formula to count the concatenated dates in another thread? Would it not be much easier simply to count the Rs in the first place...

See the attached file.

Thank you very much for your great solution but:
1- I don't want helping cells or columns,the formula depends on cell "AE1"
2-"Aggregate" formula doesnot work in office 2007,Can you replace it?
3-Question:How do you depend on the same cell in your formula?or How does this formula work?

Can you answer my other questions,please?

You can amend my suggestion to be compatible with Excel 2007, e.g. like this

=SUBSTITUTE(TEXT(SUM(IFERROR(SMALL(IF(B3:AC3="R",B$2:AC$2+0),{1,2,3,4,5,6,7}),0)*100^{6,5,4,3,2,1,0}),"00+00+00+00+00+00+00+")&TEXT(SUM(IFERROR(SMALL(IF(B3:AC3="R",B$2:AC$2+0),{8,9,10,11,12,13,14}),0)*100^{6,5,4,3,2,1,0}),"00+00+00+00+00+00+00"),"+00","")

...but now an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

As I said before, this will cope with up to 14 rest days (in any number of days) - do you have people with more than 14 rest days in one month?

See attached

I am sure that this will be rejected by you... along with every other suggestion that has been made so far.

It is as long as it needs to be. Not a character longer. Not a character shorter. It is DLLs array formula from a couple of posts earlier adapted to cope with 31 rest days in a month.

Use it or ignore it. It's up to you. When you do reject it, it might be nice to get an answer to my Qs back at Post 25, which you have, so far, chosen to ignore.

It would have been nice to at least acknowledge the others who have tried to help you in this thread. I think that some of them are unlikely to help you again, as you have completely ignored their contributions here.

Yep. He's on my "Ignore" list, for sure. I was playing with DLL's solution - to understand it and to learn to change/adapt it. I hadn't intended to post it; but when I saw the OP's second bump... I couldn't resist the temptation.

FINALLY, the OP is happy - with a solution rejected a few days ago - and WILL accept one, solo, solitary, lonely, individual helper cell.

Great!! Well done Phuocam. Have some rep!!

Originally Posted by leprince2007

I wanted to make a small change for this formula but no one helped me

Well, they did, but you ignored them.