How to add an expiring timer to Excel that works

How to expire Excel files & why macros & scripts don’t work

The different types of expiring timer options in Excel, why VBA scripts and macros are useless, and how to expire spreadsheets effectively with expiry dates that cannot be deactivated or defeated.

Microsoft Excel spreadsheets often contain information that you do not want to be available for an indefinite period.  Some common reasons are if the data is confidential, you are providing a workbook as a free trial, or you do not want users to reference old information.  This tutorial will cover the limited and ineffective options Excel and Excel 365 offer for expiry and how to add an expiring timer that cannot be defeated.

   How to add an expiring timer to Excel

Excel doesn’t have a built-in feature for expiry timers, but using VBA code to set one is technically possible.  You must be aware, however, that VBA-based security is easy to break.  Newer versions of Excel disable macros by default for security reasons to prevent malicious code from being executed, meaning users must choose to enable macros before their document expires.  There are some ways to mitigate this, which we’ll discuss later, but know that they introduce their own problems.

   How to add an expiring timer to Excel based on a date

The easiest type of expiring timer to implement in Excel is a set expiration date.  For example, you want your Excel workbook to become inaccessible on 1/1/2029:

1. Open your Excel sheet and press Alt + F11 on your keyboard to open the visual basic editor.  In the sidebar, double-click on “ThisWorkbook”.
   
2. Paste the following code into the editor window:

   Private Sub Workbook_Open()
     Dim exp_date As Date
     exp_date = "12/31/2020" 'update this
     If Date > exp_date Then
       MsgBox ("Spreadsheet has expired.")
       ActiveWorkbook.Close
     End If
     MsgBox ("You have " & exp_date - Date & " days left")
   End Sub

   You can change the file expiration date to whenever you like.
   

3. Press Ctrl + S to save the changes.  When a user next opens your document, provided they have macros enabled, it will check the date against the user’s system clock and close the Excel workbook with the expiry message box “Spreadsheet has expired” if it is after your expiry date.
   

Obviously, this solution has major flaws:

1. If a user does not have macros enabled then it will not work.
2. If a user changes their system clock back to a previous date, then they will regain access to the spreadsheet again.

  How to add an expiring timer to Excel based on the first open

A set date expiry is fine for some use cases, but if you want to grant a user a trial of a sheet or aren’t sure when they’ll access it for the first time, you’ll need something a bit more dynamic.

You can use VBA to expire the file a set number of days since the user first opened it:

1. Open your Excel spreadsheet and press Alt + F11 on your keyboard to open the visual basic editor.  In the sidebar, double-click on “ThisWorkbook”.
   
2. Paste the following code into the editor window:

   Private Const DAYS_UNTIL_EXPIRATION = 30
   Private Sub Workbook_Open()
   
       Dim ExpDate As String
   
       On Error Resume Next
       ExpDate = Mid(ThisWorkbook.Names("ExpDate").Value, 2)
       If Err.Number <> 0 Then
           ExpDate = CStr(DateSerial(Year(Now), _
           Month(Now), Day(Now) + DAYS_UNTIL_EXPIRATION))
           ThisWorkbook.Names.Add Name:="ExpDate", _
           RefersTo:=Format(ExpDate, "short date"), _
           Visible:=False
           ThisWorkbook.Save
       End If
   
       If CDate(Now) > CDate(ExpDate) Then
           MsgBox "Your trial period is over.", vbOKOnly
           ThisWorkbook.Close savechanges:=False
       End If
   
   End Sub

   You can change file expiration by changing the DAYS_UNTIL_EXPIRATION to whatever number of days you want the document to be valid for.
   

3. Press Ctrl + S to save the changes.
4. When a user next opens your document, provided they have macros enabled, it will store when they first opened it.  Then, each time it is opened after that point, the document will check if the user’s system date has passed the relevant number of days.  If it has, it will dispaly an Excel expiry date warning/notification.
   

   How to add an expiring timer to Excel 365 (Excel Online)

Expiry timers in Excel 365 or Online are set through OneDrive.  This is better since it checks the date against a server rather than the user’s PC clock.  However, it’s still not very effective because it’s easy for users to make copies of the spreadsheet before it expires or save it as another file type.

If you want to add the timer regardless, here’s how to do so:

1. Press the three dots next to your spreadsheet in OneDrive and choose “Share”.
   
2. Click the pencil icon next to the “To” field and select “Sharing settings”.
   
3. Change “Can edit” to “Can view” and select your expiry date in the DD/MM/YYYY field.  Ideally, you should also choose to share it with specific people rather than via link.  Press “Apply”.
   

   The problem with Excel expiry timers

There are various issues with setting an expiry date in Excel using the approaches outlined above:

1. VBA scripts are easy to break.  Most likely they won’t be enabled anyway since most organizations consider them a security threat.
2. Even when they execute, users can simply change their system date to get around them, or save the workbook to a different file format and then back again.
3. Adding VBA code to every spreadsheet you want to share is not very user-friendly.
4. Editing and copy-paste protection in Excel is not adequate, making it too easy for users to create copies of documents without expiration dates.
5. Excel Online’s expiry is poorly implemented.  Users can create duplicate copies to their personal OneDrive which will not expire, or change the file format.

So, as a security mechanism, Excel expiry is useless.  You should only be looking to use it as a reminder or warning to indicate to users that information is too outdated to use.

   How to make your Excel spreadsheets expire effectively

Locklizard does not let you add an expiring timer directly to Excel files (we only support the PDF file format), so you have to save to PDF first.  However, once you have done so, you can expire files regardless of their location, both online and offline, without resorting to useless macros or VBA scripts.  You can even change expiry dates after distribution, and prevent copy and paste, editing, prints, screenshots, and saving to other file formats, so that users cannot bypass expiry controls.

There are several options for expiry included in the Safeguard Writer application:

1. In the “Expiry & Validity” tab, you can tick “at” to expire after a certain date and/or “after” to expire a number of days since first use.  Note that you can change file expiration after protection and distribution from the Safeguard Admin system.
   
2. In the “Printing & Viewing” tab, you can tick “Limit number of views to” to expire the PDF after a certain number of opens or views.  Or, choose “Allow Printing” and tick “Limit number of copies to” and “No access after print copies depleted” to expire after a specific number of prints.
   
3. As well as having files expire, you can also expire user access to them.  For a complete overview of expiry options see PDF expiry and How to make PDF documents expire.

Unlike with other solutions, users cannot create unprotected copies of your document or change their system clock and, therefore, cannot bypass expiry controls.

  The best way to expire your Excel spreadsheets

The expiry controls users can enforce with Excel’s VBA scripts are unfortunately not worth the code editor they’re written in.  Due to recent changes, Excel now requires user input to enable macros, meaning expiry dates only work if users agree to them.  In other words, the only option users have to create expiry timers in Excel are useless.  The only real choice is to enforce expiry dates through third-party software that also prevents editing, copying, printing and saving to other file formats, so that expiry restrictions cannot be bypassed in any way.  PDF DRM software such as Locklizard Safeguard is therefore a natural choice.

As well as expiry dates that cannot be bypassed or removed, Locklizard enables you to:

• Effectively prevent editing, screenshots, printing, and saving
• Add dynamic watermarks that cannot be bypassed or removed
• Expire documents based on a date, number of days since last open, number of prints, or number of opens
• Manually revoke document or user access at any point
• Lock documents to devices, countries, and IP addresses
• Track document opens and prints, including by whom and at what time

The key thing to remember is that unlike Excel spreadsheets, Locklizard controls cannot be removed.  Using a combination of encryption, secure licensing, and DRM controls, our software effectively prevents users from removing expiry dates and stops them from making non-expiring copies.

Take a 15-day free trial today to see how Locklizard can expire your Excel spreadsheets effectively while protecting them against sharing and misuse.

   FAQs

How do I insert a countdown timer in Excel?

By adding the following code to a module using VBA:

Private Sub Workbook_Open()
    Sub timer()
        interval = Now + TimeValue("00:00:01")
        If Range("A1").Value = 0 Then Exit Sub
        Range("A1") = Range("A1") - TimeValue("00:00:01")
        Application.OnTime interval, "timer"
End Sub

And writing the amount of time you want (i.e. 24:00:00) in cell A1. You can then press Alt + F8 with the cell selected to start the countdown.  However, bear in mind that this countdown does not enforce any expiry etc – it is just visual.

Can I write an expiration formula in Excel instead?

Technically, you could make a defined name and a formula that fetches the current date.  You’d then have to reference that in each of your formulas, protect the workbook with a password, and then protect all worksheets with formulas that reference the defined name, too.  This way, when the spreadsheet expires, your formulas get replaced, and certain functionality stops working.  However, this is a very intensive method that users would be able to bypass by just removing the editing password or uploading the file to Google Sheets.

Most of the references you’ll find to expiration formulas online are intended to use conditional formatting to highlight expired food products in a spreadsheet, not prevent access to the spreadsheet itself.

Can I use Excel to expire without VBA?

You could probably write an external COM module and use that or find another add-in or module.  However, this is very unlikely to be effective or consistent because even if the expiry itself is effective it won’t stop users from making unexpired copies of the sheets.

Does Locklizard expire Excel without VBA?

Yes.  We don’t use any VBA scripts or macros in our software since they are simple to defeat.

Can I add effective expiry dates using a different spreadsheet software?

As far as we are aware, no.  Open Office and other applications still rely on non-native code to create an expiration date which can be bypassed by changing the system clock, creating a copy of the sheet, etc.

Do expiration date scripts work?

No.  They are simple to disable or bypass, even for expired dates.  Users can disable macros or JavaScript or save files to different file formats and back again.

Most organizations however disable macros and JavaScript by default since it is a security risk for malicious code execution.  However, in the unlikely event this functionality is enabled, it is easy for users to disable again.

Can Locklizard add expiry dates to xlxs files?

No.  We only protect and expire files in the PDF format.

Do I need to use Excel for expiration date tracking with Locklizard?

No.  The Safeguard Admin system tracks expiry dates automatically and lets you know when files will expire and which files have expired.

Can I change file expiration after distribution?

Yes.  Using Safeguard, you can change file expiration at any time even after a file has expired.

Can I make user access to a file expire rather than have the file expire?

Yes, Safeguard can do both.  A file can be set to expire or not, and user access to that file can also be set to expire.  That way for example, you can have a file that never expires but access to it expiring at different times for each user.