User Profile
JTrehus
Copper Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Excel formula - Show value in dedicated cell based on various values in multiple cells.
HansVogelaar 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.Re: Excel formula - Show value in dedicated cell based on various values in multiple cells.
HansVogelaar 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.Excel formula - Show value in dedicated cell based on various values in multiple cells.
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
