round

[Guest]

Here is what I am trying to do

I have 2 columns that have been formated and formula added to calculate the difference between 2 times. e.g 22:00-01:00 equals 3 hours. The problem I am having is when the times are 22:00 to midnight or 00:00. It will not show anything. So I have to input either 23:59 or 00:01, the problem with this is that the resulting time shown is either 2.98 or something similar

How do I get it to round to the nearest quarter hour

Bobby

 

[Paul]

Here is what I am trying to do.

I have 2 columns that have been formated and formula added to calculate

the difference between 2 times. e.g 22:00-01:00 equals 3 hours. The problem
I am having is when the times are 22:00 to midnight or 00:00. It will not
show anything. So I have to input either 23:59 or 00:01, the problem with
this is that the resulting time shown is either 2.98 or something similar.

How do I get it to round to the nearest quarter hour.

Bobby

Post the formula you are using and someone may be able to help.
In principle, there's no reason why 00:00 should not work correctly.

 

[Guest]

I have tried to put 00:00 and the formula works ok except instead of the cell showing 00:00 it is blan

Bobby

 

[Paul]

What is your formula?

Also, when replying, please quote sufficient text from the post to which you
are replying for us to see what you are replying to!

 

[Jerry W. Lewis]

Excel times are stored as fractions of days; e.g. 22:00 is internally
stored as 22/24=.916667 (as you will see if you format to general). As
a result, subtraction of times is just ordinary subtraction. Thus Excel
should calculate 22:00-01:00 to be 21:00, not 3:00, unless there is some
associated date information that you failed to mention.

If you just have simple times (without specific date information), A1 is
a start time, and B1 is an end time, then
=IF(B1<A1,1+B1-A1,B1-A1)
should do the trick.

Jerry

 

[Guest]

=(c12-b12+(c12<b12))*2
where c12 is the start time and b12 is the finish time

Bobby

 

[Guest]

That formula is no different to the one I already use. If I put 00:00 into the cell the cell does not show anything, it is blank

Bobby

 

[Paul]

What formula? Please include at least some of previous post when replying!

That formula is no different to the one I already use. If I put 00:00 into

the cell the cell does not show anything, it is blank.

 

[Peo Sjoblom]

Use 24:00 instead of 00:00

also you can shorten your formula from

=(c12-b12+(c12<b12))*24

to

=MOD(C12-B12,1)*24

round to nearest quarter

=(ROUND(MOD(C12-B12,1)/"0:15",0)*"0:15")*24

or

=(ROUND(MOD(C12-B12,1)/(1/96),0)*1/96)*24

format result as general or else you'll get 0:00

--

Regards,

Peo Sjoblom

That formula is no different to the one I already use. If I put 00:00 into

the cell the cell does not show anything, it is blank.

 

[Jerry W. Lewis]

Your formula does not do what you said in your original post.

For example

finish start your_formula
1:00 22:00 21 should be 3
22:00 1:00 3 should be 21

The IF formula that I gave in my earlier post will handle these
correctly. You can multiply my IF formula by 24 to display hours
instead of time.

=24*IF(B12<C12,1+B12-C12,B12-C12)

But, if the issue is just that you may need more than 24 hours for
totals, you can use the custom format "[h]:mm" and stick with Excel times.

As Paul suggested, further replies should contain enough context that
someone who does not have the entire thread in front of them can figure
out what you are asking.

Jerry

 

[Guest]

Thanks the advice to input 24:00 instead of 00:00 worked a trea

Bobby