Persistent Time Calculations in Excel

[ictponder]

Does anyone know how to calculate a number based on persistent time?

Example:

cell value 1[1200] / cell value 2[3] = cell value 3[400] cell value 4[result]

every hour [1200] will decrease by [3] for [400] hours

after one hour it would look like this

[1197] / [3] = [399]

after 2 hours

[1194] / [3] = [398]

I want to be able to increase cell value 1 at any time, thus increasing the value of cell value 3. Whenever cell value 3 changes by 1 and only by 1 I want [result] to increase by a number i choose. I want the file to do calculations automatically every hour on it's own. The only input will be cell value 1 and it will only ever be increased.

[Alex3000gt]

You can call up the date or time, hit the F1 button to see how to do that because I can't remember how to do it. For now let's just assume it is "TimeFxn", but you'll get the actual way to do it after poking around a bit.

Then I think you can do something like =hour(TimeFxn) and this should just pull the hour part. You'll need some coding in there to say basically

IF hr(TimeFxn)=Whole Number

Then

Blah blah blah

End



Okay, so this post wasn't all that helpful, but I hope it gets you moving in the right direction.

As far as making the file itself autonomously do the math on it's own, I dunno.

[19Eclipse90]

From: can I see continuously updated time in excel column? - Microsoft Community

Hi,

ALT+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste all the code below in on the right.

Run the sub 'StartClock' to start the clocl and ''StpoClock' when you want to stop it.

Dim Go As Boolean
Sub StartClock()
Go = True
MyClock
End Sub

 

 

Sub MyClock()
If Go Then
Worksheets("Sheet1").Cells(1, 1).Value = Format(Now, "hh:mm:ss")
Application.OnTime (Now + TimeSerial(0, 0, 1)), "MyClock"
End If
End Sub

 

 

Sub StopClock()
Go = False
End Sub

Doing that will put the time constantly updating in cell A1. I might add, that is pretty freaking cool!

Use =hour(A1) anywhere to gather the change of hour, as Alex stated. You'll probably also want to use =day(A1), =minute(A1), and =second(A1) to gather those details. This breaks up the time into the four values you need or want to use to run the calculation over a 400 hour span. Let's say we put these in C1=day, D1=hour, E1=minute and F1=second.

The way I'm seeing it -- and this is with no coding experience so the program would be, IMO, labor intensive -- you can lay out your days and hours such that specific cells will calculate at a given time and only at a given time. In the line below, C1=25 because that's today's date -- assuming you'd start today -- and D1=## because, depending on the hour, you may want something else calculated.

=if(and(C1=25,D1=##,E1=0,F1=0)...),

Basically, C1 and D1 are hard-coded (C1=[25,26,27,28,29,30,31,1,2,3...] and D1=[0...23]) individually in reference cells and, if you're looking for on the hour every hour, you want the minute and second to always be zero. Doing it this way, I can't see putting in only one value to make things go; I see it requiring at least a couple, including the one value and the date. Would likely take up a rainy day to write something like that, assuming what you actually mean to calculate isn't that complicated.

Interesting question, though, and if anyone really knows, I'm curious now.

EDIT: I already see a problem with what I've suggested, since everything is relying on time. Unless there is a way to "capture" values at a specific time, I don't know how to get around losing all the data when the time changes. I found this and, while I can't personally make odds or ends of it, it seems like it might help: http://www.ozgrid.com/forum/showthread.php?t=61383.

[wingnut]

Sure...