Need help completing an IF function with date & time as inputs

Using the attached doc, and its legend tab, I need the following columns corrected as I am seriously tripping up on formats vs inputs for outputs.

1.) Column "N's" result based on the duration output of the preceding column "M" is:
<=4 = 40
5 = 30
6 = 20
>=7 = 10

2.) Column "Q's" result based on the duration output of the preceding column "P" is:
<=18 == 40
>=19<=24 == 30
>=25<=48 == 20
>=49 == 10

The legend tab shows the info as well.

Let me know if anyone needs further explanations.

Because you have this formula in column M:

=TEXT(L6-H6, "[h]")

you should realise that this will return a text value. It might be better to use this formula instead in M6:

=--TEXT(L6-H6, "[h]")

as the double-unary minus (--) will convert that to a proper number. Then one of the formulae that you have tried may work for you.

Hope this helps.

Pete

Interestingly enough, that trick (don't get it) does enable my calculation column produce the correct result, but I am not understanding the translation.

When I did that my numeric value for "4" hours changed to 96 (Why did it choose 24 as a denom?). Is there a way to augment either or or both cells so that I see the true time value and still calc the correct grading metric?

Yes, you just need to format cell M6 as General (or Number with 0dp) and you will see the value 4 instead of 96:00:00.

To understand what is happening, you need to understand how Excel stores dates and times internally and how formatting can change the appearance of a value (but not the underlying value itself). Excel stores dates as a whole number - the number of elapsed days since a particular reference date (actually 1st January 1900). So if you put today's date (16th Jan 2014) into a cell somewhere, it will display as 16/01/2014 (for me - probably as 01/16/2014 for you). However, if you then apply a General format to that cell, you will see the number 41655. That is the underlying value, but formatting can be applied to the cell to have the value displayed in a different way (that makes a bit more sense to us).

Now, times are stored as fractions of a day, and as a day has 24 hours then the underlying value for a time of 12:00noon is 0.5. Similarly, 6:00am has a value of 0.25, and 6:00pm has a value of 0.75, and so on. You can enter these time values into a cell and then change the format to General to see the underlying value.

In your case, the formula that I gave you returns the value 4. However, the cell was formatted as [hh]:mm:ss. For your original formula, the display in the cell was 4 as this was really the text value "4", and formatting has no effect on text values. But in changing to my formula, the formatting clicked in and the value of 4 (interpreted by Excel as meaning 4 days, because it is an integer) was displayed as 96 hours.

So, it's all very logical, but it can be very confusing - hopefully this has helped to explain things.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to click on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you), in order to pass on your thanks to those that have helped. This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete