Excel spill formula days of the week to actual dates

[MichielS340]

Hi, hopefully someone can give me a little push in the right direction.

I have a table regarding holiday hours with years and weeks on the rows and in the columns the different days of the weeks (see attached).

How would I be able to prepare a list of dates (including employee number and holiday code on the row) from such a table in order to compare that to another table that already contains dates?

The formula to derive the actual date from the column is on the right but not sure how to prepare a flat table from it.

Many thanks in advance

[#2]

@MichielS340 

You've had over 80 views without a reply. Usually that kind of "silence" suggests an unclear query. I think that's definitely the case here. Your sample spreadsheet shows that you have a fair amount of skill in Excel (the formula in the yellow cell is not one that a novice would create).

HOWEVER, you leave a lot of things (things that no doubt are clear to you) unexplained.

• You note that Table 1 is to be compared with Table 2, but compared HOW?
• Table 1 apparently shows holiday hours but "how are they shown" is left for us to hypothesize. My guess is that the 8s, 3s, 4s in some of the cells means those are holiday hours, but it's a guess. 
• Table 2 has a column of holiday codes, but what the codes represent (what is holiday 123?) is left to the imagination.

How would I be able to prepare a list of dates (including employee number and holiday code on the row) from such a table in order to compare that to another table that already contains dates?

"list of dates" would be "a table" too; from "such a table"...to "compare to another table" -- again, I know you know what you mean, but it's hard to follow all the table references, some of which currently exist, some to be created...which is which; what exactly do you have in mind for the ones to be created?

The formula to derive the actual date from the column is on the right but not sure how to prepare a flat table from it.

And what would this "flat table" be, using that formula--which, as I've said, is quite a sophisticated formula--actually look like? Or, in other words, what do you have in mind for the output of all this?

Granted, it may not be necessary to know the answers to those questions to come up with the solution you're seeking. (Why, you might legitimately ask, should I care what holiday 123 is?) BUT,  just speaking for myself, I find that it helps to have more clarity on what the various details mean, what the big picture is. And I suspect I'm not alone, that that's at least part of why over 80 people have taken a look at your inquiry and chosen to pass it by.

[#3]

@mathetes true i was not clear in my previous post. Basically what I want to achieve is for the upper table to loop through all values (hours noted in F5:L11), put those in rows and add the following information on those rows:

- the concurring date (which can be derived by the formula I added) and 

- the employee number 

- the holiday code

i thought a spilling formula would be a nice solution in such a situation but I was not able to achieve it with map and lambda and use the cell references of each cell. 

I adjusted the attachment to be more clear with the example output.

Many thanks, 

[#4]

@MichielS340 

I'm going to have to let this percolate a bit. And maybe, if we're fortunate, one of the regulars here with more advanced skills than I, will be able to look at this and resolve it with that lambda you referred to.

I've looked at your revised sheet and now see (or think I see) what you're looking for as the output. (Although it's not totally clear, since you've worked through only the first two example rows, and many of the remaining are for the same week as the first employee, so it's not clear whether they should be arrayed in parallel with the first (extending to the right for the same days) or down below. Either way, it could be confusing.)

Are you looking for total hours of holiday time taken for any given day, or totals for employees? 

What, may I ask, is the source? Why are the weeks such that your formula, right off the bat, subtracts 1 from the number of the week? Could the raw data be brought in differently (specifically, with each day in its own row)? That might even enable the use of a Pivot Table to summarize the data. I do think that's part of the difficulty in getting a dynamic array function to work.

[#5]

@mathetes I gave it another try and came up with the attached. However I think it might be more efficient to first prepare the data with power query and put the days on the rows.

[#6 · Accepted solution]

@MichielS340 

I think it might be more efficient to first prepare the data with power query and put the days on the rows.

That may be. I work in a Mac environment and don't have power query.

Can you share what the raw data is? In its raw form.