Increase cell value by 1 every 43.8 seconds

[MagicMan]

Hi Guys,

I'm trying to see if there is a formulaic method of increasing a cell value by one every 43.8 seconds (I'll settle for 44 seconds)

Assuming we start the clock at at A4 10:00:00.00 and cell B4 = current time, when
when cell B4 = 10:00:43.8 cell C4 = 1 (or when Cell B4 = A4 + 00:00:43.8)
when cell B4 = 10:01:27.6 cell C4 = 2 (or when Cell B4 = A4 + 00:01:27.6)
when cell B4 = 10:02:11.4 cell C4 = 3 (or when Cell B4 = A4 + 00:02:11.4)
etc.

Cell B4 would need to constantly show current time (unless there's a way of taking it of the PC clock).

Any ideas?

Would prefer formula but if it needs to be a macro, then so be it, but I'd need to ensure it continues running when the sheet isn't active

Many thanks

Terry

[Special-K]

Not with a formula that I know of.

[ConneXionLost]

How about:

Please Login or Register  to view this content.

Cheers,

[MagicMan]

Hi ConneXionLost.

That works. (I needed to add in a 'Recalc' macro that I found).

This might sound a bit dense, but how did you achieve the figure of 0.000506944444444444?

I will probably have to do something similar for other things but with different timings

Magic Man

Cancel that, I worked it out (remember those days in school when you never thought you'd ever use Algebra again???)

Rate in seconds/Day in seconds = Rate in seconds expressed as a decimal figure.

:-)

[Special-K]

"Assuming we start the clock at at A4 10:00:00.00"

So A4 is increasing as time goes by?
How are you doing that?

[MagicMan]

This is the code I found from stack overflow Here and adapted:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I've also attached my workbook with it working to give you a head start. You'll need to run the macro Recalc. Also, when you want to stop it, run the macro Endtime.

I changed the Range to B4 as that's what I needed. Set the Start time in A4 then used the formula given to me by ConneXionLost. The counter in C4 then tells me how many times 43.8 seconds have passed since the start time.

I then copied it into Row 5 so I could change the start time, or change the frequency (rate as a decimal) or both. :-)

I'm looking to create a 'Dynamic Dashboard' that would say something like.

Information Security defences block malicious e-mails.
Since 10:00 am this morning [-output from C4 -] emails have been blocked.
[-output from C5 -] have been blocked today

You could then do similar for rejected access requests etc. etc. etc.

Hope it helps.

I changed the

[Special-K]

Ah, OK, that makes sense now.
I thought you meant you wanted to produce a working clock using formulas only.

[MagicMan]

I thought you meant you wanted to produce a working clock using formulas only.

That was always a thought. But I knew it'd probably be a vain hope...

What can I say, I'm an optimist