(070709) verify use of TIME Function, Find Quantity Level compare to time-day.

If I can get feedback on correct use of the following, thanks.
Hello, have asked this one for awhile, got answer: try WORKDAY()
was / is the wrong answer. Have been trying to find the acceptable value of
a quantity, at any given time of day, compared to a set total quantity.

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

with being told "it cant be done" / Time Function didn't just jump out at
you in help... / otherwise I ask you, is this correct?

I think this is all you need, which returns TRUE/FALSE

=CT9(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

I don't see why you need the 100% if you are simply trying to establish
whether or not the rate of production is above/below that expected in
relation to elapsed time vs total (shift) time.

HTH

"nastech" wrote:

(070709) verify use of TIME Function, Find Quantity Level compare to time-day.

If I can get feedback on correct use of the following, thanks.
Hello, have asked this one for awhile, got answer: try WORKDAY()
was / is the wrong answer. Have been trying to find the acceptable value of
a quantity, at any given time of day, compared to a set total quantity.

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

with being told "it cant be done" / Time Function didn't just jump out at
you in help... / otherwise I ask you, is this correct?

Thank you much, working on this long time, no responses.. if you have
corrections for the following, else is for others.. :)

What you wrote seems like the right answer, will test if does the same
thing;
- as part of another OR'd "Volume" formula, where todays volume was not yet
factored. (where this example should still work)
=CT9(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

- for a 3 level cond. format volume levels, lo-mid-hi, does the following
sound correct? Measure is against CU9, using fixed / absolute cells:

cond. format good to remote this to 1 absolute cells?: (3 levels this to L5
/ L6 / L7
=(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*M5 / M6 / M7

cond. format will say: CT9$L$5 or $L$6 or $L$7

-----

"Toppers" wrote:

I think this is all you need, which returns TRUE/FALSE

=CT9(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

I don't see why you need the 100% if you are simply trying to establish
whether or not the rate of production is above/below that expected in
relation to elapsed time vs total (shift) time.

HTH

"nastech" wrote:

(070709) verify use of TIME Function, Find Quantity Level compare to time-day.

If I can get feedback on correct use of the following, thanks.
Hello, have asked this one for awhile, got answer: try WORKDAY()
was / is the wrong answer. Have been trying to find the acceptable value of
a quantity, at any given time of day, compared to a set total quantity.

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

with being told "it cant be done" / Time Function didn't just jump out at
you in help... / otherwise I ask you, is this correct?

typo, get rid of the (greater than) sign at front:

=(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*M5 / M6 / M7

=(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*M5 / M6 / M7

thanks for your answer, I have found 1 problem with my formula; where max
time is supposed to be 1600 or 4pm, if I download data after 4 pm (but last
volume occurs at 4pm), I get an inflated value because of $DC$3 time stamp
is later than 4pm. not sure if just because of the changes made for the
conditional formatting, don't think so, this is what I am doing:

fixed cell $L$4: =(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*$M$4
and the volume column cond. format top condition (blue):

fixed cell $M$4: merely has minimum level to meet for that condition
cond. format, CT9: CT9=$L$4

all of this may not have been neccessary to tell, but a time later than 1600
in $DC$3 inflates the minimum level.

QUESTION: Is the best answer to OR(CT9=$L$4,CT9=$M$4)

thanks
-----

"Toppers" wrote:

I think this is all you need, which returns TRUE/FALSE

=CT9(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

I don't see why you need the 100% if you are simply trying to establish
whether or not the rate of production is above/below that expected in
relation to elapsed time vs total (shift) time.

HTH

"nastech" wrote:

(070709) verify use of TIME Function, Find Quantity Level compare to time-day.

If I can get feedback on correct use of the following, thanks.
Hello, have asked this one for awhile, got answer: try WORKDAY()
was / is the wrong answer. Have been trying to find the acceptable value of
a quantity, at any given time of day, compared to a set total quantity.

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

with being told "it cant be done" / Time Function didn't just jump out at
you in help... / otherwise I ask you, is this correct?

is this correct:

=((IF(NOW()<TIME(16,0,0),$DC$3,TIME(16,0,0))-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*$M$4

Found answer, previous is wrong, but included shorter way of doing (what
correction is);

=((IF($DC$3<TIME(16,0,0),$DC$3,"16:00")-"9:30")/("16:00"-"9:30"))*$M$4

"Toppers" wrote:

I think this is all you need, which returns TRUE/FALSE

=CT9(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

I don't see why you need the 100% if you are simply trying to establish
whether or not the rate of production is above/below that expected in
relation to elapsed time vs total (shift) time.

HTH

"nastech" wrote:

(070709) verify use of TIME Function, Find Quantity Level compare to time-day.

If I can get feedback on correct use of the following, thanks.
Hello, have asked this one for awhile, got answer: try WORKDAY()
was / is the wrong answer. Have been trying to find the acceptable value of
a quantity, at any given time of day, compared to a set total quantity.

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

with being told "it cant be done" / Time Function didn't just jump out at
you in help... / otherwise I ask you, is this correct?

... better ?

=((MIN($DC$3,TIME(16,0,0))-"9:30")/("16:00"-"9:30"))*$M$4

"nastech" wrote:

Found answer, previous is wrong, but included shorter way of doing (what
correction is);

=((IF($DC$3<TIME(16,0,0),$DC$3,"16:00")-"9:30")/("16:00"-"9:30"))*$M$4

"Toppers" wrote:

I think this is all you need, which returns TRUE/FALSE

=CT9(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

I don't see why you need the 100% if you are simply trying to establish
whether or not the rate of production is above/below that expected in
relation to elapsed time vs total (shift) time.

HTH

"nastech" wrote:

(070709) verify use of TIME Function, Find Quantity Level compare to time-day.

If I can get feedback on correct use of the following, thanks.
Hello, have asked this one for awhile, got answer: try WORKDAY()
was / is the wrong answer. Have been trying to find the acceptable value of
a quantity, at any given time of day, compared to a set total quantity.

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

with being told "it cant be done" / Time Function didn't just jump out at
you in help... / otherwise I ask you, is this correct?

Thanks.. will use less space.. you believe I've been trying to figure this
out / probably first asked ~2 years ago.... will make sheet accurate.

"Toppers" wrote:

.. better ?

=((MIN($DC$3,TIME(16,0,0))-"9:30")/("16:00"-"9:30"))*$M$4

"nastech" wrote:

Found answer, previous is wrong, but included shorter way of doing (what
correction is);

=((IF($DC$3<TIME(16,0,0),$DC$3,"16:00")-"9:30")/("16:00"-"9:30"))*$M$4

"Toppers" wrote:

I think this is all you need, which returns TRUE/FALSE

=CT9(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

I don't see why you need the 100% if you are simply trying to establish
whether or not the rate of production is above/below that expected in
relation to elapsed time vs total (shift) time.

HTH

"nastech" wrote:

(070709) verify use of TIME Function, Find Quantity Level compare to time-day.

If I can get feedback on correct use of the following, thanks.
Hello, have asked this one for awhile, got answer: try WORKDAY()
was / is the wrong answer. Have been trying to find the acceptable value of
a quantity, at any given time of day, compared to a set total quantity.

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

with being told "it cant be done" / Time Function didn't just jump out at
you in help... / otherwise I ask you, is this correct?

thanks for your help, the following reduction seems to work

=((MIN($DC$3,"16:00")-"9:30")/("16:00"-"9:30"))*$M$5
=((MIN($DC$3,TIME(16,0,0))-"9:30")/("16:00"-"9:30"))*$M$5
=((IF($DC$3<TIME(16,0,0),$DC$3,"16:00")-"9:30")/("16:00"-"9:30"))*$M$4

"Toppers" wrote:

.. better ?

=((MIN($DC$3,TIME(16,0,0))-"9:30")/("16:00"-"9:30"))*$M$4