Calculating time difference then rounding down
 

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.

Calculating time difference then rounding down
 

Try,


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

Mike


"Angel" wrote:

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.

Calculating time difference then rounding down
 

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")


--
HTH

Bob

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

"Angel" wrote in message
...
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.

Calculating time difference then rounding down
 

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

"Angel" wrote:

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.

Calculating time difference then rounding down
 

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

"Mike H" wrote:

Try,


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

Mike


"Angel" wrote:

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.