to calculate credit days based on time slabs

hii.. attached here is query image..

Kindly help me with the required formula.. I think mod function will work but how to use it with time is a bit tricky task for me..

Thanks..

Well, based on your file the logic appears straightforward however I'd like to know how negative time will be recorded?

In general terms, unless you're running a Mac or have specifically set your date system to 1904 you can't enter negative time (in time format), so you would need to format as general / number
e.g. -12:00 = -0.5

I'll just assume the above is the case... in which case:

Formula:

Please Login or Register  to view this content.

yes i have set to 1904 date system

OK, understood, in which case the prior suggestion should work for you -- that question was to check to see if you were planning to enter negative time as text (e.g. '-8:30)

Working fine for positive time but wrong results for negative time..kindly check & revert..

Instead of -1,-1 & 2 ..0,0 & -1 should be the answer..

apologies! perhaps

Formula:

Please Login or Register  to view this content.

Excellent..but 1 more small issue noticed now..

The formula provided for slabs:

Hours Credit
< 8 0
>=8 <16 1
>=16 < 24 2
>=24 < 32 3

I wanted slabs as follows:

Hours Credit
upto 8 0
>8 to 16 1
>16 to 24 2
>24 to 32 3

OK, so perhaps:

Formula:

Please Login or Register  to view this content.

Perfect!! Thanks a lot Xlent..