Can I make a sum which returns a min value?

[ashcrroft752]

Hello All,

I need to make a calculation of hours, it needs to return 8.5 hours as a minimum and needs to subtract 1 hour if 9.5 hours are exceeded.


Column A is start time, column B end time column C is where I need the calculation of A & B with a minimum of 8.5 hours subtracting 1 hour after 9.5 hours have been reached.

I'm not sure this is even possible to do, it's beyond my limited knowledge but I'd like to learn how to do this.

[martindwilson]

do you want real time or decimal time?
=MAX((B1-A1)*24-1,8.5) is a decimal hours value format cell general w.ll give a min of 8.5
=MAX((B1-A1)*24-1,8.5)/24 will give a min of 8:30
hmm what if someone does 9.25 hours

[Kevin UK]

Hi ashcrroft752

If your times are as hh:mm

Please Login or Register  to view this content.

[ashcrroft752]

Hi All,
I would need the time retuned in column C to be decimal, so 8 1/2 hours would be 8.5.

If some one were to do 9.25 hours this should return 8.5

[Kevin UK]

Hi ashcrroft752

If it is "Time" in A1 & A2: =(MAX((B1-A1)-TIME(1,0,0),TIME(8,30,0)))*24

If it is "Decimal Time" in A1 & A2: =MAX((B2-A2)-1,8.5)

[martindwilson]

did you not try
=MAX((B1-A1)*24-1,8.5)

[ashcrroft752]

Hi All,

Both work until the start time is before 00:00 and the end after 00:00, is it possible to make C return nothing if no values are entered in A & B?

[Kevin UK]

Then you can use the MOD function for the real time:

Please Login or Register  to view this content.

And for the Decimal Time:

Please Login or Register  to view this content.

And to return "nothing". one way might be something along these lines!

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

You can also not show zero's in cells that have a zero value. not sure where this is in excel 2003!