divide an integer with time (in format "hh:mm")

[lelkes]

Hi again!
This question continues the the thread http://www.mrexcel.com/forum/excel-...ime-remaining-meetween-now-tomorrow-8-am.html

For the best part of the last six hours I've tried to figure out how to proceed, but failed completely So once again, I've to ask for your help....

Let's say I want to calculate the infusion rate in ml/h

A	B	C	D
1	Opened	Time Remaining (hh:mm)	Volume in ml
2		=(TODAY()+1+"08:00:00")-NOW()
3	=NOW()		350

<tbody>
</tbody>

Rate in ml/h

=D3/?

<tbody>
</tbody>

The formula I came closest with:

=D3/(TEXT(C2;"hh:mm")*24)

But not exactly

Thanks in advance, as usually your answer will be duly appreciated
//Peter

 

[bschwartz]

What is the result of C2 with the new formula when you format the cell as general?

And by the way, 5:30 is not equivalent to 5.3 hours. It's 5.5 hours.

 

[lelkes]

Thanks Mike!

To do the division don't you need a singe unit (minutes or seconds) and then convert the result?
so it would be 350 ml / (373/60) h yielding 56.3

time remaining is 5:16 =5*60+16=316
rate = 350/(316/60) =66,5 ml/h
rate = 350/5.16 = 67,8 ml/h
so your solution don´t give a match

 

[lelkes]

What is the result of C2 with the new formula when you format the cell as general?

And by the way, 5:30 is not equivalent to 5.3 hours. It's 5.5 hours.

I knowthat it´s not bschwartz

Now when time remaining 05:08 C2=((TODAY()+1+"08:00:00")-NOW())*24 =29.14

 

[bschwartz]

So the ML/h is 12.01?

 

[bschwartz]

=D3/C2 should work if that is the correct time difference in hours.

 

[lelkes]

So the ML/h is 12.01?

Yes, but shouldn´t it be round 70?
350 ml in 5 hours = 350/5 = 70 ml/h

 

[lelkes]

=D3/C2 should work if that is the correct time difference in hours.

It IS the correct time difference in hours...

 

[bschwartz]

Yes, but shouldn´t it be round 70?
350 ml in 5 hours = 350/5 = 70 ml/h

I was going on this where you said there was 29.14 hours as a result in C2:

Now when time remaining 05:08 C2=((TODAY()+1+"08:00:00")-NOW())*24 =29.14

I think what's happening here is you are being fooled by Excel, which is formatting a difference in time, which should be a number of hours, as a time value (5:00). Really your formula is showing a 29 hour difference, but in time formatting, Excel ignores whole days, so 1 day and 5 hours (29 hours) is showing up as 5:00.

The correct answer is that the time difference between tomorrow at 8:00am and NOW() is 29 hours. (I'm not sure what time zone you're in, but that is the result of the formula according to Excel). Thus 350/29 is the correct ML/H which is 12.

 

[lelkes]

I was going on this where you said there was 29.14 hours as a result in C2:



I think what's happening here is you are being fooled by Excel, which is formatting a difference in time, which should be a number of hours, as a time value (5:00). Really your formula is showing a 29 hour difference, but in time formatting, Excel ignores whole days, so 1 day and 5 hours (29 hours) is showing up as 5:00.

The correct answer is that the time difference between tomorrow at 8:00am and NOW() is 29 hours. (I'm not sure what time zone you're in, but that is the result of the formula according to Excel). Thus 350/29 is the correct ML/H which is 12.

It+s working on my computer...

I found a solution:
C2=formated hh:mm
C3 = h+(mm/60)
rate = D3/C3

Now I just have to figure out the formula

 

[lelkes]

I found the solution:

C2=(TODAY()+1+"08:00:00"-NOW())*24
C3=HOUR(C2)+(MINUTE(C2)/60)

rate is =D3/C3&" ml/h"