Nested if statement

[no_namer]

Hi,
I am not sure how to write the formula which will return the meal allowance and night differential rate for each of the employees who worked overtime.

Regular working days and hours are : M-F, 8-5 pm

An employee is entitled to receive a $5 meal allowance if:
- On Holidays and Weekends : Overtime worked went beyond 12nn or 7pm (a total of $10 if worked before 12nn until past 7pm;
- On Regular days (M-F): Overtime worked beyond 7pm

The night differential rate is earned if overtime worked is rendered beyond 10pm on any day.

I have attached a sample worksheet that I am working on.

Thank you.
No_namer

[teylyn]

Don't assume that everyone knows your terminology.

what does 12nn mean?

To make things easier for testing, please provide a few sample cases and manually mock up the expected results.

What days are holidays? What is the "night differential rate"? Is this granted on top of overtime rates or in exchange of these?

You need to provide more information.

[no_namer]

Sorry for the vague details.
Anyhow, 12nn is 12h00. The night differential rate is 10% of an employee's hourly rate if he/she rendered overtime work from 22h00 until 06h00 the next day.

Further, on top of the criteria mentioned earlier for an employee to receive a meal allowance, he/she must have been hired before 05Jan2005. Details on the employees' hiring dates are also inlcuded in shee 2.

I've updated the excel attachment. This now reflects the hourly rate of an employee based on his/her pay class. Each pay class level is subject to 10 steps or increments. This means that even if the employess does not get promoted, his/her pay increases yearly until he reached the last step of his pay class level.

I've also included the multiplier rate applicable as:
For overtime worked on Monday-Saturday 1.5x (Overtime earned=Hourly rate x OT hours X mulitplier)
Holidays and Sunday 2x

Holidays are the statutory holidays observed within the calendar year. To facilitate the writing of the formula, holidays are:
25Dec10 Christmas day
01Jan11 New Year's day
11Oct10 Canada day

I made a simple "if" statement for the multiplier column. But this does not capture the holiday dates as I do not know how to tag them as such, because holidays can fall on any day.


Thank you.

[teylyn]

Have you mocked up the expected results and explained the rules that lead to those results? If so, I don't quite get it.

[JulieM]

Holidays are the statutory holidays observed within the calendar year. To facilitate the writing of the formula, holidays are:
25Dec10 Christmas day
01Jan11 New Year's day
11Oct10 Canada day

I made a simple "if" statement for the multiplier column. But this does not capture the holiday dates as I do not know how to tag them as such, because holidays can fall on any day.

Hint brazenly borrowed from this thread: http://www.excelforum.com/excel-gene...-a-column.html

Create a named list "holidays" somewhere in your spreadsheet that defines all of your holiday days, obviously this would have to be updated annually.
Then add in a column between "B" and "C" (which you can hide after editting) with the formula in cell 7 onwards =COUNTIF(holidays,A7)Then modify your multiplier formula, which is now column G, to =IF(B7="Sunday",2,IF(C7=1,2,1.5))
HTH

[no_namer]

Hint brazenly borrowed from this thread: http://www.excelforum.com/excel-gene...-a-column.html

Create a named list "holidays" somewhere in your spreadsheet that defines all of your holiday days, obviously this would have to be updated annually.
Then add in a column between "B" and "C" (which you can hide after editting) with the formula in cell 7 onwards =COUNTIF(holidays,A7)Then modify your multiplier formula, which is now column G, to =IF(B7="Sunday",2,IF(C7=1,2,1.5))
HTH

hi JulieM,
Thank you for response. Your suggestion worked!
I am now working on the Night Differential and Meal allowance columns. Each of these has three criteria to meet and I cannot write those in a single statement.
Would really appreciate any suggestions.

Thank you again.
No_namer

[arthurbr]

Hi no_namer and julie

please refrain from quoting entire posts. This makes threads longer and difficult to follow.
Quote only what is relevant to your reply - Thx for helping us keep the forum tidy !

[JulieM]

@ arthurbr - apologies, I just wanted to make clear which part of the problem I was addressing.

@ no_namer - Just wanting to check that the ONLY time periods to be entered on this spreadsheet would be hours eligible for overtime payments? Could you upload your latest version please?

[no_namer]

@ no_namer - Just wanting to check that the ONLY time periods to be entered on this spreadsheet would be hours eligible for overtime payments? Could you upload your latest version please?[/QUOTE]

Hi Julie, yes, you are right. The purpose of this form is to facilitate that filing and computation of the overtime worked.

I uploaded the latest version, inclusive of your suggestion on holiday. I've added few columns to incorporate the policies governing the computation. The columns in colors are the ones I'm having difficulty writing the formula. I placed some notes/comments on the spreadsheet to describe my formula and the difficulties encountered. thank you.

@arthur: apologies and will your advice in mind.

thanks everyone for your help!

[JulieM]

Hi no-namer - I don't know if my computer has got its' metaphorical knickers in a twist, but I can't open your latest version direct from the forum and if saved to my desktop it only opens if I change the extension to .xls; at which point it seems to be your original version. I'm sure the problem is sat at my pc, but could you upload it with a different name? Sorry!

[no_namer]

Hi no-namer - I don't know if my computer has got its' metaphorical knickers in a twist, but I can't open your latest version direct from the forum and if saved to my desktop it only opens if I change the extension to .xls; at which point it seems to be your original version. I'm sure the problem is sat at my pc, but could you upload it with a different name? Sorry!

Hi JulieM,
I've attached the file under a new file name.
Thank you,
No_namer

[JulieM]

Hi no_namer
Sorted the nightime differential, thanks to your statement in cell I29

I've uploaded my amended version of your file - with additions and changed columns highlighted in yellow.
As you can see, I've not tried to do the whole if... /lookup in one go (thats not how my mind works), instead I've worked out if the times are eligible for OT, then how many hours are eligible, then looked up the £ due. You'd probably want to hide columns F,H and N in use.

HTH

edit - ignore file in this post, contains typo, see next post.
? mod able to delete this one please?

[JulieM]

Hi no_namer
attached file corrects typo in the previously attached file, with nightime differential fixed.
Also calculates the meal allowance now.


edit: see next post

[JulieM]

here you go: final version including travel allowance criteria in a new column
By using separate columns for the dfferent criteria, you can more easily change things if company rules change, rather than having to re-work multiple nested "ifs".
Sunday lunch beckons.....

PS I think I've proof read all my formulae properly now, obviously you need to check them too!
Happy Halloween


edit: further tweak for max travel

[no_namer]

PS I think I've proof read all my formulae properly now, obviously you need to check them too!

Hi JulieM,
thank you for your assistance.

However, I would like to understand better the formula in Columns N to S. I tried different scenarios to test the validity of the conditions and unfortunately, the expected valid value did not return.

I've attached the file again with comments.

I can't thank you enough for helping me.
No_namer

[JulieM]

a) you haven't attached a file
b) by using multiple columns, I have broken the formulae into simple steps, which one do you not understand?
c) there is one scenario by which N7...N11 would return an invalid FALSE result but this is not indicated by the comments/scenarios in otform.xls (nor allowable under EWTD!)
d) all the tests I have applied return the expected results as I have interpretted the comments/scenarios that you attached to the file otform.xls (for example formulae in column Q depend on my interpretation of comment I29)


I have used names as much as possible in the formulae so hopefully you can easily make the required amendments where your company regulations do not fit my interpretation of your comments
kind regards J

[no_namer]

hi JulieM, please see attached file, my inquiries are in the file...
Thanks,
No_Namer

[JulieM]

I can't comment on the formula in column N since that is not the same as the one I proposed!
However, if you believe that only the time out is of significance,feel free to remove the other element from the formula, you know your company rules, I don't!

One of the two Mondays is a bank holiday, whereas the other is not, I understood from your criteria that this required a different multiplier? Again, easy for you to remove the extra condition if it only needed the same multiplier.

[no_namer]

Hi JulieM,
Apoligies, I was playing with the formulae in Column N. You originally wrote this for Column N: =OR(F7>=19,H7>19,H7<8). I would really appreciate if you could explain the formulae. Column F contains the converted Time In while H7 contains Time Out.
I might find column 7 irrelevant but it will help me a lot if I understand the logic behind yours.
Thanks a lot,
No_namer

[JulieM]

Having split the calculations down into single small steps, I didn't think my logic was that tortuous to follow!
the "OR" returns TRUE if any of the (3) conditions within the brackets are true and is based on your statements "if an employee worked beyond 7pm ..[on any day].." and "Regular working days and time are Monday to Friday, 8-5pm"

It returns the expected result for every combination of start/end time that I put in. So can only assume we have different interpretations of your eligibility statements.
What start/end times are giving you inappropriate TRUE/FALSE results for this formula? For clarity, suggest you write an example of the times and expected outcome in the thread please, not in another s/s

HTH