Forum Discussion
Excel formula - Show value in dedicated cell based on various values in multiple cells.
See the attached sample workbook. I used a list of public holidays in The Netherlands; you can edit the list as needed.
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.
See the attached sample workbook. I used a list of public holidays in The Netherlands; you can edit the list as needed.
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.
Do you also need to take end times after midnight into account? For example
Start time = 20:00
End time = 04:00
- If working over midnight (0:00) these hours need to filled out in a new row.
Example:
Working on 04.02.2022 from 20:00 to 04:00, the hours from 0:00 to 04:00 need to be filled out in new row with correct date.
- Hi Hans,
Thank you again (Hartelijk bedankt),
This is a amazing setup with formula's i never could figure out myself....
Thanks again for the quick and helpful support.
Have a nice day.
Jeroen