Help with time please.

[Floyd Forbes]

I have this formula that caluclates
time"=IF(K27-E25+F25-L27<=0,0,K27-E25+F25-L27)".
in cell G25. It works fine if E25 is 11:00 PM and F25 is 11:30 PM. If I try
to calculate E25 is 11:55 PM and F25 is 12:05 AM it gives me 0:00. What is
wrong?

Floyd

 

[Pete_UK]

This also depends on your values of K27 and L27 (I suspect these are
dates), but think of 12:05AM as being 0:05 - it is less than 23:55
(11:55PM), and as you are subtracting one from the other the result is
less than 0, so your formula returns 0.

Hope this helps.

Pete

 

[Floyd Forbes]

How do I get my formula to work? The answer should be 0:10.

 

[Ragdyer]

You haven't mentioned what the other cells in your formula are supposed to
be doing, so, are we supposed to *guess* at what's in them?

Just going from the information you gave us:
E25 is 11:55 PM
F25 is 12:05 AM
And you want a return of 10 minutes,
This will work in a cell formatted to h:mm,

=MOD(F25-E25,1)

If this doesn't help, post more info.

 

[ilia]

I have an application that calculates times for a timesheet. Some
shifts are overnight, thus you run into a similar problem. Although it
may be somewhat inefficient, here is my approach.

Column B always containts time in
Column C always contains time out
Desired value in the same row for Column E should be a fractional value
of hours.

For simplicity, I create two hidden columns to the right of the
timesheet, labeled Hour In and Hour Out. The formulas, respectively,
are:
=HOUR($B143)+MINUTE($B143)/60
=HOUR($C143)+MINUTE($C143)/60

Then, the hour calculation (Column E) has the following formula:
=IF(M143>0,IF(M143>=N143,N143+24-M143,N143-M143),N143)

Where Column M and Column N are Hour In and Hour Out, respectively.

Hope that's helpful.

-Ilia