Hi,
I would like to get some help with finding a correct formula for the following cases.
Case #1
If text in A3 shows 'Monday' i would like to get the autosum of D3-C3 visible at E3.
But if A3 shows 'Sunday' i would like to get the autosum of D3-C3 visible at G3.
Case#2
But what if i work 1 hour overtime on a monday.....?
If text in A3 shows 'Monday' i would like to get my worked hours between 08:00 to 16:00 (normal hours) visible at E3 and 1 hour visible at F3 (from 16:00 to 17:00).
I hope someone can help me.
Have a nice day!
Jeroen
Good morning Hans,
Thanks a lot for your quick reply.
The formula is working nicely, I only had to replace the comma (,) with a semicolon (;) in the whole formula.
Is it also possible to get the same results based on time of the day instead of amount of worked hours.
Example:
Normal hours: between 08:00 and 16:00.
Overhours 50%: between 16:00 and 21:00.
Overhours 100%: between 21:00 and 08:00.
Working on a Monday from 12:00 to 18:00 will result in 4 hours normal hours (E2) and 2 hours 50% (F2).
Working on a Monday from 12:00 to 22:00 will result in 4 hours normal hours (E2) and 5 hours 50% (F2) and 1 hour 100% (G2).
To make it even more complicated....
Is it possible to overrule these formula's based on date (column B).
Example:
Christmas is on a Wednesday, then all worked hours (any time of the day) need to be visible in column Overtime 133% (H3).
Meaning that 25.12.2022 and 26.12.2022 need to override all other formula's.
I know it's much more complicated then my first question, but i hope it is possible.
Have a nice day.
Greetings Jeroen.
It may be possible but it won't be trivial. Using 365 insider beta my formula was
= MAKEARRAY(2,4,
LAMBDA(d,shift,
LET(
s, INDEX(Start,d),
e, INDEX(End,d),
wd, IF(WEEKDAY(INDEX(Date,d))=1,1,2),
shiftS, INDEX(bandS,wd,shift),
shiftE, INDEX(bandE,wd,shift),
laterStart, IF(s<shiftS, shiftS, s),
earlierEnd, IF(e<shiftE,e,shiftE),
hrs, earlierEnd - laterStart,
IF(hrs>0, hrs, 0)
)
)
)I have used a Lambda helper function to produce all the results as a single formula but, using traditional Excel, it should be possible to pick the references out as relative references for each cell. The Let would need to be replaced by a nested formula but that is not my scene.
Good morning Hans,
I have been 'playing around' with the file you submitted me.
Working nicely, also with the holidays.
But i still have some minor issues with some formula's. I tried to adjust some formula's but as this is far above my knowledge i had to give up....
Hope you can help me here one more time.
As long as the values in column C are below the max normal working hours (16:00), the table shows the correct values in the correct columns E, F, G and H.
But if i insert a starting time above the max value for normal working hours, the result of the formula is a negative time, showing ##### in column E.
And at the same time the result of the formula calculating the overtime 50% in column F is showing a incorrect value.
I would like to get the automatic results in the overtime columns when starting after 16:00 and the column for normal hours showing 0.
I apologize if i haven't been clear from the beginning of this interesting conversation....
An other question is: How can i implement the Saturday also as overtime 100%.
Greetings Jeroen.
Since this thread raised its head again, I attempted to match @Hans Vogelaar 's solution but restricting myself to a single dynamic formula and absolute references only. I realise this is of no value to the OP and only a few readers will have the necessary version of Excel.
I started by using SCAN but came to the conclusion that I was going to need MAKEARRAY to display the resulting 'array of arrays'. I therefore chose to switch to MAKEARRAY from the outset. The result was a solution process that very much reflects the legacy spreadsheet strategy of developing a formula for a single cell and copying across and down the relevant number of cells. The difference is that, rather than relying upon implicit intersection, I used a representative row and column index within the result array to determine the values to use within each calculation.
I seem to remember that @Sergei Baklan regards MAKEARRAY as somewhat ugly, but I don't think it is that bad used when used to feed values into a Named Lambda function. Something else that caused me grief with a single formula solution is that the early morning hours need to be combined with the evening hours when calculating hours worked at double time. The mechanics of this calculation were to use MMULT but I chose to hide the formula step by defining a further Lambda function
CombineNightHrsλ.
= CombineNightHrsλ(
MAKEARRAY(ROWS(Date),5,OvertimeHoursλ)
)
With a bit of luck, by the time these functions come out of beta, I might know what I am doing!
At least, from Hans's sheet, I now know what the first and second days of Christmas are in Dutch; I never made it past 'dank je wel' before.
Accepted Solutions