SOLVED! Multiple IF Function In A Single Formula

[serdal22]

Dear Friends,

This is my first post, but it won’t be the last. Sorry for the inconvenience ;-) patience is needed 

I am trying to complete my “Wage Calculator” program. I am done by %95. The only part remains is the “Sea Time” calculation. I searched to see if similar topics were already discussed but I couldn’t see anything through search engine, and decided to post this question.

I am using MS Office 2003. Now my sea time . . . I work several shifts here at Seabus. Some shifts are 4 hours, some are 10 hours, and 10.5 hours. Some shifts are 7 to 8 hours, as well.

My sea time I am gaining when I work (I work 1 week on, 1 week off) is particularly important when it comes to keep my license (Marine ticket) up to date (Continuance Of Proficiency) . I have to work at least 1 year every 5 year period, then I go to harbour master (Transport Canada) to extend it for another 5 years as per “Sea Time” I have gained for the current 5 year period.

Now, the sea time calculation. As per the collective agreement (Between the union and the company), here are the break times we are entitled to take as per the length of our shifts: (Please note that when I am on my break I step out of the Seabus and go to land (Terminal) and that break time is not counted as sea time because I am not on the Seabus).

a – No break for the 4 hour shift; so 4 – 0 = 4 hours of sea time.
b – 3 breaks for the shifts of 10 hours or more; so 10 – 1.5 = 8.5 hours of sea time. (Please note that each break is 0.5 hour).
c – 2 breaks for the shifts more than 7.5 hours but less than 10 hours; so 7.5 – 1 = 6.5 hours of sea time.
d – 1 break for the shifts of 7.5 hours or less but more than 4 hours; so 4.5 – 0.5 = 4 hours of sea time.

As seen in the examples, I am trying to calculate the sea time hours by entering into related cells the length of my shifts. Here is the formula I have used:

=IF($M2>=10,$M2-1.5,IF(7.5<$M2<10,$M2-1,IF(4<$M2<=7.5,$M2-0.5,IF($M2<=4,4))))

The formula works for 4 hours, for 10 and 10.5 hours. For some reason, when I enter 5 (hours) on the column L it gives “False” error.
Basically what I am trying to do is to use multiple IF functions in a single formula. If you could help me with this, I will be grateful to you.

I am still amateur and love Excel a lot for being able to make calculators and programs. I am also working on my Ship Cargo Loading and Stability Program as well. I will post some harder questions regarding to my that program later.

I have (Tried) to upload my wage calculator. I am working on Sept 2010 sheet. When you click on L2 down to L8 cells, you'll see what I mean.

Very Best Regards . . .

Capt. Serdal
CDN Master Mariner, North Vancouver, Canada