Calculating time difference then rounding down

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angel - 17 May 2007 08:59 GMT

Hi,

I think what I'm looking for is probably really simple but I just can't seem
to get it right!

Basically, I currently have an amount of hours in cell F56 (12:00), I want
to deduct the times shown in G56 (08:24) and H56 (01:00) and express the
outcome as a period of time. Therefore, my formula is currently shown as:

=TEXT(F56-G56-H56,"HH:MM")

This gives me an outcome of 02:36 - so far so good. I now want to amend the
above formula so it still does the same calculation but rounds the total down
to the nearest 15 minutes - thus giving an outcome of 02:30. I think this
will probably need to include FLOOR somewhere but I can't figure out
where/how.

Can anybody shed any light on this? Many thanks in advance.

Reply to this Message

Mike H - 17 May 2007 09:09 GMT

Try,

=ROUND(SUM(F56-G56-H56)*96,0)/96

Mike

> Hi,
>
[quoted text clipped - 14 lines]
>
> Can anybody shed any light on this? Many thanks in advance.

Reply to this Message

Angel - 17 May 2007 09:16 GMT

Mike, thanks so much for this - what's taken me hours has just been resolved
by you in minutes - amazing!

> Try,
>
[quoted text clipped - 20 lines]
> >
> > Can anybody shed any light on this? Many thanks in advance.

Reply to this Message

Bob Phillips - 17 May 2007 09:13 GMT

I don't know why you are using Text rather than just format it, but

=TEXT(ROUND((F56-G56-H56)*96,0)/96,"HH:MM")

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi,
>
[quoted text clipped - 17 lines]
>
> Can anybody shed any light on this? Many thanks in advance.

Reply to this Message

Mike H - 17 May 2007 09:15 GMT

Angel,

I may have misunderstood, if you always want to round down then use:-

=FLOOR(SUM(F56-G56-H56)*96,1)/96

But if you want to round to nearest then use the previous formula.

Mike

> Hi,
>
[quoted text clipped - 14 lines]
>
> Can anybody shed any light on this? Many thanks in advance.

Reply to this Message