Making a calculation/formula expire after set time

[Woof]

Hi,
I'm wonderind if anyone can help me with a more robust formula than
currently have.

I want to have a price list where the formula only generates vali
prices for a defined period of time.

Currently my formula does this.
1) Takes =now() as the current time
2) compares =now() to my set 'EXPIRY TIME'
3) IF =NOW() < EXPIRY TIME = TRUE, if =NOW() > EXPIRY TIME = FALSE.
4) IF TRUE = Display price, IF FALSE = DISPLAY "PRICE EXPIRED"

The problem is, the user only has to change the system time and pres
F9 to work around this.
For this project i can't access a external data source to harmonis
data.
And i don't have any knowledge of VBA.

Is there any other way of doing this?
Does anyone have any suggestions for a similar method which is mor
robust.

Any help would be much appreciated

 

[Keith R]

Is your primary concern people resetting their system clocks? How is the
data (price) used by a user?
Is it is used as part of an order form, I would just add the now() date and
price expiry date to the form, indicating that it has to be received by the
expiry date in order to valid - that way they can change a system clock, but
if they don't submit the form before the expiry date, you don't have to
honor it.

Of course, I'm not sure I understand what you are doing; if someone wanted
to break into your formulas and change the expiry date, it wouldn't be that
much harder than changing the system clock in the first place?

 

[Woof]

The spreadsheet is used by a sales team to calculate prices when out o
the office, (without connection to the office network)
The prices fluctuate and only remain valid for short periods. On
hidden sheet factors are updated which influence the price. Onc
updated, a new copy of the spreadsheet is distributed.

What I am trying to do is set an expiry time so people cannot ge
prices using old versions of the spreadsheet. I’m trying to force the
into always using the current version. Currently I use my formula s
after a set date the price is not displayed.
But my formula is easily worked around, but changing the system date.

Its not the security I’m too concerned about, rather making sure peopl
use the correct costings, by preventing the old versions fro
displaying a price after a set time. People do use older versions t
get a keener price and I’m just trying to make things as fool proof a
possible

 

[Keith R]

One (limited) approach would be to look at the date the workbook was saved,
and if the system date is not
saved date < now() < saved date+10
then don't show the price.

However, someone could still change the machine date to be /within/ that
range and get a price.

Even if you always looked at the 'last accessed date' a sneaky person could
always change the system date/time to be a few minutes after the last time
it was used, and continue to use the workbook much longer than desired.

If this is your sales force changing the dates, I'd be more inclined to
suggest dealing with this as a human performance issue, but I understand
your desire to make an 'error-proof' system. Unfortunately, if you can't
access an external reference time, I'm not sure how to hide prices in a
fool-proof way.

Other options may depend on how much control you have over the salespeople's
PCs- if you set a registry value to the current workbook version you might
be able to ensure that (after uploading a newer workbook) that all older
versions of your workbook won't work anymore. Again, a sneaky person who's
PC you don't have access to could just install old workbooks on multiple PCs
so an older version would always be available somewhere.

I think the easiest answer is still to put your expiry date on any forms or
reports that show the price, so even if someone tries to use an old price,
the expiry date will still show that the invoice shouldn't be accepted by
your shipping/receiving dept, because it is submitted after the expiry date
of one or more of the item prices used. One way to automate this would be to
have your workbook create a copy of each invoice that is created (password
protected against changes) that could be emailed in; have a central Excel
program on your server to load all invoices from a central file area
(hourly) and if any invoice includes an expired code, automatically reject
the invoice, maybe even automatically send an email back to the salesperson
who submitted it.

HTH,
Keith

 

[Woof]

*One (limited) approach would be to look at the date the workbook wa
saved,
and if the system date is not
saved date < now() < saved date+10
then don't show the price.*

Thanks for all your help
This is something i can work with to make it that little bit harder t
work around.
Thinking along the same lines, is it possible to write to an cell whe
a document printed?
I have macros set up to print and export to htm, the key information
need.
If i could tweak my macros to also record the this date i will hav
another date to reference.

Thanks once again

 

[Keith R]

Sure- just use the workbook_beforeprint event, and
use now() to get the system time and put it in the desired cell
something like (untested):

Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets(1).Range("B16").Value = Now()
End Sub

put it in the workbook module, not in a specific worksheet.
The default name in the IDE is "ThisWorkbook" in the
project view.

format the cell to display it however you want (should only
have to do this once, then each time the cell is populated, the
formatting stays the same).

Of course, if you were already worried about someone
changing the system time...the time that will be put in that
cell is still the (modified) time, so it won't help you
detect misbehavior in those kinds of situations.

HTH,
Keith

 

[Keith R]

One other thought ;-)

instead of using the Workbook_BeforePrint....

you could use a macro on your machine only to paste
the value from now() into a hidden cell/workbook (A1),
then in neighbor cells put in something like:
B1:
=now()
C1:
=IF(and(B1>C1,B1>A1),B1,C1)

now every time the workbook calculates, B1 is updated
with the current system time, and C1 shows the highest
system time based on the date you put in and the system
date...

then if you could evaluate and if C1 is _higher_ than either
your creation date, or the highest system date seen so far,
you would know that someone was messing with the clock.

This is a step more sophisticated than the
saved date < now() < saved date+10
because you are updating a cell with the system time each
time the workbook calculates, so even a small move
backwards of the system clock would be caught.

Still not nearly as sophisticated as using a registry setting or
some other method, just another low-tech idea.