Forum Discussion
Excel spill formula days of the week to actual dates
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.
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,
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.
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.
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.