Time in a formula

[djleason]

I want to do two things to the cells in column X. I want the value of cell ‘X3’ to automatically decrease by the value of cell ‘N3’ at the exact same time of day every day using the computer’s clock. I also want the value of cell ‘X3’ to automatically increase by the value of (cells ‘E3 * P3’) but only when the value of cell ‘E50” is increased and by the amount of that increase. What would be the formula for that? This is to keep a running total of widgets on hand
Dana

 

[sanrv1f]

Hello Dana,

welcome to the board,

I want to do two things to the cells in column X. I want the value of cell ‘X3’ to automatically decrease by the value of cell ‘N3’ at the exact same time of day every day using the computer’s clock. I also want the value of cell ‘X3’ to automatically increase by the value of (cells ‘E3 * P3’) but only when the value of cell ‘E50” is increased and by the amount of that increase. What would be the formula for that? This is to keep a running total of widgets on hand

looks a bit cryptic, could you show us, some sample of, what is in X3, N3, E3 and P3, and how you need them to change at what time?

 

[tusharm]

(A) You would have to use VBA code to do what you want.

(B) If that is OK, you still have to clarify what it means when you write X3 should increase by E3 * P3...but only by the amount of that increase. It seems to me that there are two different ways you want to increase X3.

I want to do two things to the cells in column X. I want the value of cell ‘X3’ to automatically decrease by the value of cell ‘N3’ at the exact same time of day every day using the computer’s clock. I also want the value of cell ‘X3’ to automatically increase by the value of (cells ‘E3 * P3’) but only when the value of cell ‘E50” is increased and by the amount of that increase. What would be the formula for that? This is to keep a running total of widgets on hand
Dana

 

[djleason]

Let me try and be more specific.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Total seeds on hand now are 117 X3 = 117
<o> </o>
Let’s say I buy 2 packages of seeds: E3 = 2
Each seed package contains 90 seeds: P3 = 90
Every Day by 6:00 PM, 6 (which is variable) seeds are planted N3 = 6
<o> </o>
By 6:01 PM tomorrow the value at cell X3 should = 117 – 6 or 111
By 6:01 PM the next day the value at cell X3 should = 111 – 6 or 105
By 6:01 PM the next day the value at cell X3 should = 105 – 6 or 99
This continues every day without fail
The next day I am offered a deal on seeds, (which doesn’t happen daily) so
I buy another 2 packages of 90 seeds: 2 times 90 = 180 or E3 times P3 and on this day
by 6:01 10 more seeds(N3) are planted so X3 should = (99 +180)-10 or 269
<o> </o>
I want the ‘seeds on hand’ or X3 to be correct at all times after 6:30 PM
I am not familiar with VB code but I willing to learn

 

[tusharm]

Two questions.

1) After the 1st purchase of seeds, why did X3 not change?

2) After the 2nd purchase of seeds, what will be in E3 and P3?

One comment.

You may be able to automate this but for the amount of work you are doing, it would be overkill and the solution will not be very robust. Instead, I would use a different approach.

In 3 columns, track the purchases: Date, Package Count, Seeds/package.

In 2 columns track the planted seeds: Date, #seeds.

Now, the difference betweeen the sums of the 2 sets of columns is the inventory on hand. A lot simpler, much more robust, easy to audit, easy to maintain, and easy to implement.

Let me try and be more specific.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Total seeds on hand now are 117 X3 = 117
<o> </o>
Let’s say I buy 2 packages of seeds: E3 = 2
Each seed package contains 90 seeds: P3 = 90
Every Day by 6:00 PM, 6 (which is variable) seeds are planted N3 = 6
<o> </o>
By 6:01 PM tomorrow the value at cell X3 should = 117 – 6 or 111
By 6:01 PM the next day the value at cell X3 should = 111 – 6 or 105
By 6:01 PM the next day the value at cell X3 should = 105 – 6 or 99
This continues every day without fail
The next day I am offered a deal on seeds, (which doesn’t happen daily) so
I buy another 2 packages of 90 seeds: 2 times 90 = 180 or E3 times P3 and on this day
by 6:01 10 more seeds(N3) are planted so X3 should = (99 +180)-10 or 269
<o> </o>
I want the ‘seeds on hand’ or X3 to be correct at all times after 6:30 PM
I am not familiar with VB code but I willing to learn

 

[djleason]

Well, that's no fun. I really wanted it to be automated but... Okay. Thanks for your help. If I figure out how to do the time thing, I ll let you know.
djleason