Function to take place on certain date/time

[martyloo]

Hi guys,

Does anyone know if I can create a function which will calculate after a certain period of time?

for example - on the 16/06/2012 at 6:00pm in cell A1 automatically do the function =sum(A2+A3)
on the 17/06/2012 at 6:00pm in cell B1 automatically do the function =sum(A2+A3)

Cells A2 and A3 are constantly updating with data and everday at 6:00pm I would like the data noted down in each row

Can anyone help???

 

[hitch_hiker]

just a few thoughts, how does the sheet constantly update at 6pm, are you expecting row 2 and 3 only to have data, will the cell with the formula just keep on going down, so in 2 months time the cell is for example d60, then in another 2 months d120?
if the page is being fed from another program, could you use a 'on sheet change' style of event?

 

[martyloo]

just a few thoughts, how does the sheet constantly update at 6pm, are you expecting row 2 and 3 only to have data, will the cell with the formula just keep on going down, so in 2 months time the cell is for example d60, then in another 2 months d120?
if the page is being fed from another program, could you use a 'on sheet change' style of event?

I have a web query linked to the net so the spreadsheet constantly updates every minute from 8am - 4:30pm then it stops untill the next day. I just wanted to take a note of what it stops at each day.
Yes the cell with the formula will just keep going down so in 2 months it will go from d60 then another 2 months d120.

Im not sure what an "on sheet change" style of event is?

 

[hitch_hiker]

I am no expert, I am trying some ideas, does the workbook run 24/7 or does it get turned of every day and subsequently opened every day? And the 6pm is an arbitary time ,or is there a specific reason you asked for 6pm,

 

[martyloo]

I am no expert, I am trying some ideas, does the workbook run 24/7 or does it get turned of every day and subsequently opened every day? And the 6pm is an arbitary time ,or is there a specific reason you asked for 6pm,

Yes it gets turned off everyday, and opened everyday. No reason its 6pm can be anytime.

Any ideas?

 

[hitch_hiker]

I'm almost certain you can't update a worksheet while a macro is running, which would mean you couldn't simply do a loop until time = 18:00, so I'm trying to establish an 'event' which could trigger a macro, the macro would be relatively easy. I know nothing about web queries, can it be made to call a macro?

 

[martyloo]

I'm almost certain you can't update a worksheet while a macro is running, which would mean you couldn't simply do a loop until time = 18:00, so I'm trying to establish an 'event' which could trigger a macro, the macro would be relatively easy. I know nothing about web queries, can it be made to call a macro?

It doesn't need to trigger the macro if need be I can open it and trigger it myself everyday I dont think that should be a problem providing its fairly straight forward

 

[hitch_hiker]

I'll set something up tomorrow, and send it to you,

 

[martyloo]

I'll set something up tomorrow, and send it to you,

Perfect cant wait to try it out

 

[arkusM]

You could take a look at the Ontime function. It will alow you to schedule a trigger at a specific time provided that Excel is open, and the worksheet initiated the ontime function.