Free Microsoft Excel 2013 Quick Reference

Warning! Time has expired....

I've been given a spreadsheet and asked to come up with a way to have an alert sent via email when the difference between two dates expires. Well I found the code for sending an email, but it needs to be triggered by clicking a button.
This is the code I have for this: (Continued request below Code)

Sub Email()
Dim oApp
Dim oMess
Dim eRange As Range
Dim i As Long

Application.ScreenUpdating = False
Set oApp = CreateObject("Outlook.Application")
Set eRange = Range("A1")
Set oMess = oApp.CreateItem(0)
With oMess
.To = eRange.Offset(i, 0)
.Subject = "Your time has expired"
.Body = "Joe, Moe, and Larry" & vbNewLine & vbNewLine & _
"Please goto the Stooges spreadsheet and handle the problem."
End With
Set oMess = Nothing
i = i + 1
'stop on the first blank cell in column a or b etc
Loop While eRange.Offset(i, 0) > ""

Set oApp = Nothing
Application.ScreenUpdating = True
End Sub

My second question is how to create a expiration date, I am looking for the number of workdays between two dates.

Thank you so much for all your help.

Post your answer or comment

comments powered by Disqus

How do I write a code that will play a sound, such as the Mozzart sound clip that comes with windows, or any other music or sound clip that is on the computer after a certain time has expired in a cell.


Can anyone tell me why this code won't work..
I only want then to be able to make entries between 07:45 and 08:45.

If TimeValue(Now()) > "0:745:00" And TimeValue(Now()) < "08:45:00" Then

code if OK

code if not OK

End If

Hi, I'm relatively new to Excel and wanted to know if it's possible to format a cell so it adds 1 to the value within itself after a certain amount of time has passed?

For instance, I have a worksheet which contains a persons ID number, their name and their Learning Curve Week. I would like to have the values in the Learning Curve Week column add 1 to themselves each week automatically without having to do it each week manually - there are a lot of people in this record.

Sorry if this is such a noob question but I've searched about Google, this forum and the net trying to find a solution but the only ones i can find refer to automatically updating time and date cells.

Thanks in advance

How can I set a conditional format for a cell so that it changes colour once it has passed 30 minutes and then when it has passed 60 minutes.

Column Time entered - time put in when something is received. If it is not dealt with within 30 minutes of time received it will turn amber and if it is not dealt with within 60 minutes it will turn red. Once a time has been entered in to the next column which is time dealt with, the time entered cell will turn green.

I can set this for one change i.e. red by this formula =$G6:$G346+TIME(1,0,0)<(NOW()-TODAY())

and Green by this formula = =LEN($H6:$H346)>0

but not sure how to add another one so that at 30 minutes it turns amber and then at 60 minutes it turns red.

Hope that makes sense

Is there anyway I can have the julian date/time in the format of "08182/0500"

08-represents the year
182-represents the julian day (001-365)
/-divider for date/time (not necessarily needed)
0500-the military time

I'd like to be able to have a cell high light red once the julian date/time has expired. However, I think first I need to get outlook to view this format as a time. How can this be done? Im using Excel 2007. TIA =)

thanks to the great help of daddylonglegs i've been able to take my julian date/time in column 'D' and apply it as a standard date/time format that excel understands in column 'BL'

what i would like to have happen is when the date/time in column 'BL' is 20 minutes from expiring (based off local computers time) for it to fill in the corresponding cell in column 'D' as 'yellow'

and when the time has expired to fill the corresponding cell in column 'd' as 'red'

is this possible either as a formula or in vba? Im using excel 2007

This project i'm working on, tracks a lot of times for aircraft maintenance completion times, is there some way to have the columns that contain the times (in julian format; so... 08169/1300 , or just 081691300 (thats todays date at 1pm) and as the actual time approaches maybe 30 minutes prior to the actual time reaching 1pm, maybe highlight the time in yellow, and in red once the time has expired. i'm not sure if this is possible.. maybe using a macro that would refresh and check the computers time? i dunno... thats why i'm asking you guys. =)

ps... i cant just use time only, since maitenance can often times take days...

I could really use some help with one. I almost get it but then run smack into a wall. It's hard to explain but I'll try my best.

Cell A1 receives it's data from another application. It's a pressure reading.

The user needs to be able to adjust a control **** on a machine that in effect, changes the value of A1 by controlling the pressure. That part all works fine. The machine functions, A1 values change like they should.

As A1 changes, the values of B1 and C1 change as well.

When the desired pressure is met, I have individual control buttons that run macros to copy A1:C1 to 11 locations on the worksheet in series.

I'm trying to automate the process so that A1 is monitored and upon reaching a specified value, will run the macros in series.

To monitor A1, I use M1 as the preset value. When A1=M1 run the first macro.

At that point, the process needs to wait while an adjustment is made to the control **** and to another mechanical part. I need a short delay for the adjustments to be carried out [application.wait with a value..I have that figured out] As the adjustments are made, A1 <> M1 BUT when it again equals A1, I want the next macro to run.

Application.wait seemed like the answer but it's not because while the application.wait process is going on, A1 is sitting there in limbo and the value is not changing while the adjustments are made.

As soon as the application.wait time has expired, my current code calls the next macro and it's grabbing inaccurate data.

Any and all ideas are welcome! Please, someone that knows VB respond to me on this. I'm 99% of the way to where I need to be and there's this one last little hurdle...

Hi Guys

Please help. I am obviously failing to understand something very basic about
MsgBox syntax.
I have only previously used this function to give simple messages to the
user, and provide a single (default) OK button: E.g.

MsgBox "Your time has expired"

I want to add more buttons in due course (Yes, No, etc) but, for now, was
just trying to customise the title (caption) of the the message box:

MsgBox ("Your time has expired",vbOKOnly,"Time expiry notification")

produces a compile error ("expected: =") and I can't make sense of the Help
I am not including any Help/Context, so have left those options blank

I have tried assigning the message and Title as strings

Dim Msg As String
Dim Title As String
Msg = "Your time has expired"
Title = "Time expiry notification"
MsgBox (Msg, vbOKOnly, Title)

but to no avail..

MsgBox (Msg, vbOKOnly, Title, , ,) just produces another type of 'expected'
compile error

Thanks, Phil

PS. Once I have sorted the syntax, I should be able to use 0 instead of
vbOKOnly, 1 for vbOKCancel, etc. Right?

Hi Guys,

I am new to excel use and certainly not all that great with formulas...

After searching through all excels finacial tools, I came up empty.
What I am looking for is a formula to calculate the future value of an investment for a period of time, with daily compound interest of say 1% daily.

I am sure it is an easy thing to figure but the few tried like FV need a constant payment and I am only looking for an initial starting value with a future value after the time has expired with interest calculated...

If someone can help, it would be appreciated...


I have an app that uses Windows timer for gathering data. It uses the pop timer code in a macro that I can start at will. I have looked for a way to use OnTime Method to automatically stop the macro after a period of time has expired... but to no avail. I can start my macros at certain time of day, etc., but I have found nothing that sets a timer and then does some action (e.g. killtimer) after some duration of time has elapsed. I need to limit the data collection to 960 cells. This equates to a 4 hour trend @ 15 second intervals.

I have found some code that uses eventprocedure and ontime method to close a workbook after some definable period of time.
This raises the question, why could I not use something similar to this to stop collecting data e.g. killtimer to stop my macro?? Anyone have some idea which direction I should take on this?

Thanks again for the very fine help I have experienced on this forum. One of these days I hope to know enough to be a resource to someone else as you all have been to me.

Has anyone come up with a method for making a VBA script expire?

I.e. it will only work for a specified time and requires the author of the script to renew it at a given point?

I want to be able to do this to strengthen my position as an independent contrator financially...and copy protect my work from unwanted exploitation

Some ideas:
1-MSG box requesting a pre-defined password to renew the time for the script to work along with contact info for the author

2-Ability to define # of days, months and or years the script will work

3-Ability to define the # of days, months and or years that a MSG box warning of pending expiration will appear

4Ability to define the # of days, months and or years that the renew will extend the use of the script with multiple pre-defined passwords allowing different lengths of time extention! Perhaps an encryption method allows you to in another excel sheet (say the comfort of your own home) generate at will a password that will extend a scripts life for 3 months?! They contact you again, and again you generate a "random" password that will extend the original script

5-Anti-hacking measures, using MS's built in security

This might be a useful way of extracting monies over time, for the use of a script you provide....pre-set

Ideas thoughts, point me to work already done in this area?



I was wondering if it was possible to "Expire" a macro after a set number of runs? If it is, how would i go about doing one?

The scenario is this:
I have developed a macro which works perfectly for the job i do, and it would also work very well indeed for some other people as well. We are not in competition, but i have had a request for them to see it. What i wanted to do, was put some code in which will allow the macro to run 10 times, and after that, show a msgbox to say it has expired. (basically stick two fingers up at a friendly way)

Would this be possible??


I currently have some code which checks the dates in a range when the workbook first opens.

If the date in the range matches the current date or is older than the current date then I'd like a Userform to popup warning the user that something has expired.

I'd like to add sometimes that some of the cells in the range may be blank.

Currently the Userform pops up regardless

I hope someone can help :-)

Here's what I currently have ..... Thanks

     'Check Expiry Dates
    Dim ACell As Range 
    For Each ACell In Sheets("Information").Range("i7:i100") 
        If ACell.Value = Date Then 
            Goto AA 
        ElseIf ACell.Value < Date Then 
            Goto AA 
        End If 
    Next ACell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi there guys,

I'm still having trouble getting the following excel sheet to work correctly. A member of this forum was kind enough to provide me with a forula but I can't get it to work correctly. What I'm trying to do is, set up a spreadsheet that will calculate how long the response times of the service engineers are. However I only want it to count the hours between 8:00am & 6:00pm Monday to Friday. I've already set it so that it will turn red once the 4 hour time limit has expired. But I've got no idea how to force it to only count the hours between 8:00am & 6:00pm Monday to Friday.

I hope someone can help.

Many Thanks

Gary Hunt

I keep getting this error and i'm racking my brain trying to solve this. Can someone help me solve this error?

Run-time error '5':
Invalid procedure call or argument

Upon getting this error. The code below is highlight.

.CustomDocumentProperties.Add Name:="PathCertString", _
Value:=Environ("ComputerName"), LinkToContent:=False, Type:=msoPropertyTypeString

Below is the complete code which I think should be enough info to drum any ideas to the error.

Thanks in advance

Private Sub Workbook_Open()
Dim i As Integer, Tries As Integer
Dim storedPath As String
Dim StartTime#, CurrentTime#
Dim PassTry As String
Dim iReply As Integer
Const Pass As String = "Password"
'Integers (1, 2, 3,...etc) = number of days use
'1/24 = 1Hr, 1/48 = 30Mins, 1/144 = 10Mins use
Const TrialPeriod# = 1 '< 30 days trial
'set your own obscure path and file-name
Const ObscurePath$ = "C:"
Const ObscureFile$ = "TestFileLog.Log"
On Error Resume Next
storedPath = ThisWorkbook.CustomDocumentProperties("PathCertString").Value
On Error GoTo 0
If storedPath = Environ("ComputerName") Then
If Dir(ObscurePath & ObscureFile) = Empty Then
If MsgBox("Thank You For Your Interest in ProLogbook. Upon Completing the 30-DAY Evaluation Period," & vbLf & _
"You Will Be Required to Purchase the Unrestricted Version. Do You Agree To these Terms?" & vbLf & _
"" & vbLf & _
" YES, I Agree NO, I Disagree", vbYesNo, _
"30-DAY EVALUATION") = vbNo Then ThisWorkbook.Close Application.DisplayAlerts = False Else
StartTime = Format(Now, "#0.#########0")
Open ObscurePath & ObscureFile For Output As #1
Print #1, StartTime
Open ObscurePath & ObscureFile For Input As #1
Input #1, StartTime
CurrentTime = Format(Now, "#0.#########0")
If CurrentTime < StartTime + TrialPeriod Then
Close #1
Exit Sub
iReply = MsgBox("Your Evaluation Period Has Expired. If Prologbook Meets Your Requirements..." & vbLf & _
"" & vbLf & _
" Click 'YES' To Purchase Your Serial Code" & vbLf & _
" Click 'NO' To Remove Prologbook From Your Computer" & vbLf & _
" Click 'Cancel' If You Have Not Decided" & vbLf & _
"" & vbLf & _
"Note: You Only Have Three (3) Attempts to Enter Your Serial Code Correctly" & vbLf & _
"Before ProLogbook is Rendered Unusuable & Removed From Your Computer.", vbYesNoCancel, "Expired!")
If iReply = vbYes Then
Call isInternetAvailable
ThisWorkbook.FollowHyperlink ""
Tries = 3
For i = 1 To Tries
PassTry = InputBox("Enter Your Serial Code To Continue..." & vbLf & _
"" & vbLf & _
"Note: You Only Have Three (3) Attempts to Enter Your Serial Code Correcly Before ProLogbook is Rendered Unusuable & Removed
From Your Computer.", "Register")
Select Case PassTry
Case Pass
Rem remember new path to OK comuter
With ThisWorkbook
On Error Resume Next
On Error GoTo 0
.CustomDocumentProperties.Add Name:="PathCertString", _
Value:=Environ("ComputerName"), LinkToContent:=False, Type:=msoPropertyTypeString
End With
GoTo PassCorrect
End Select
If i < 3 Then If MsgBox(Tries - i & " Tries Remaining..." & vbLf & vbLf & " Try Again?", vbRetryCancel) = vbCancel Then Exit
Next i
Call MsgBox("ACCESS CODE OR COMPUTER" & vbLf & _
" NOT RECOGNIZED!", vbCritical, "Access Denied!")
'Workbook will close'
Dim oneSheet As Worksheet
Application.DisplayAlerts = False
With ThisWorkbook
For Each oneSheet In .Sheets
oneSheet.Visible = xlSheetVisible
If oneSheet.Name  "Logbook" And 1 < .Sheets.Count Then oneSheet.Delete
Next oneSheet
End With
Application.DisplayAlerts = True
[A1] = "Expired"
Call KillMe
Exit Sub
MsgBox " Access Approved"
ElseIf iReply = vbNo Then
'Killme code'
Call KillMe
Else: ThisWorkbook.Close Application.DisplayAlerts = False 'They cancelled (VbCancel)
Exit Sub
End If
End If
End If
End If
End Sub

I'm running out of hair to pull out. Could someone please show me the light in this hopefully easy request?

I have two cells. Both contain Date/Time values like:
Cell 1 -- 6/18/2007 7:58 AM
Cell 2 -- 6/18/2007 10:36 AM

My goal is to have cell 3 (which is currently blank) display how much time in hours has expired between the two. Like:
Cell 3 -- 2.633

Is this possible?

I have a excel spreadsheet where we keep audit dates for when equipment is
checked/audited. I have a column with the date it was audited on. Now, the
equipment need auditing again in 6mths time. Is there a way of getting excel
to let me know that date?

I also have certificates that expire after say, 3 years (for first aid) is
there a way of excel flagging up when it has expired from the date the
certificate was issued?


I have written down a function which checks for "expiration" and other
like statements from another sheet within the same workbook. It
calculates that fine if I calculate that cell be cell. However, if I
calculate for the whole workbook, the calculation bar stops at 0%. When

I go into the debug mode, it takes me to the function which I wrote for

checking expiration, and then keep repeating in the same function. I
have run step by step execution, and I have found out that the control
goes back to the start even after it passes the return statement.
Someone, please help.
Here is my code:

Function DTS_Message()
sheet_name = "DTS"
count_exp = 0
count_caution = 0
' checking for negative value, if any. That shows if the value has
expired or not.
For Each cell In Sheets(sheet_name).Range("U:V")
If cell.value < 0 Then
count_exp = count_exp + 1
End If
Next cell
'if negative value found then go for expiration message.
If count_exp > 0 Then
DTS_Message = "WARNING a Discard Time Requirement(s) has
'if not then check for other condition i.e. caution flag
For Each cell In Sheets(sheet_name).Range("Z:Z")
If cell.value = "Caution flag" Then
count_caution = count_caution + 1
End If
Next cell
'if there is a caution flag found then display this
If count_caution > 0 Then
DTS_Message = "CAUTION a Discard Time Requirement(s) is
expiring shortly!"
DTS_Message = "Discard Time Requirements are OK"
End If

End If

End Function

could anyone help in how to make a function that warns when a cell contains
a date that has expired due to a predefined timespan?

Basically what I have is a column with dates and I want to create a function
in a second column that warn whenever one of the dates are more then 3 years
old in relation to the current date.

Any suggestions?

All the best

Hi, I'm new to the board, I humbly ask for your acceptance... I work as a
cost estimator and have developed a very, very nice program to calculate the
selling price of our products. It incorporates Escalation, all direct labor
rates and overhead rates, is capable of time shifting to forward dates and
mid-points and so on. I have been "asked" by some of the people I answer to
for copies of this program, but I hesitate. If not all the adjustments are
kept up to date, the information can be eronious - and we all know "who will
be to blame" for eronious information. Here is what I want to do. I want to
have a macro that will delete all the pages then save the new file over top
of the old file. And if they have another copy - again, upon opening it, and
the statement seeing that the date has been passed - again activate the
macro. - forcing them to come back to me for another - which would be the
latest and greatest, right?
any thoughts? - Cheers to you all - and thanks for being here.

I am entering Certificate dates on a Microsoft Excel sheet. I would like an
alarm, like a red band, on the cell, when the date has passed, in other words
when the Certificate has expired.

ROY UK kindly gave me a solution regarding expiry dates on an excel spread sheet using this coding, fantastic and works the only problem is,if the security setting on someone's machine is set to high the macro doesnt work and they can disable them so the time bomb does not work , i have set auto_run but this does not solve it

Private Sub Workbook_Open()
    'check if a start date exists
    If IsEmpty(Sheets("Hidden").Cells(1, 1)) Then
        'none exists so add today's date
        Sheets("Hidden").Cells(1, 1).Value = Date
        'if a start date has been recorded, check if it is less than 30 days from start
    ElseIf Date >= Sheets("Hidden").Cells(1, 1).Value + 2 Then
        'alert user that it has expired
        MsgBox "Trial period has expired ", vbOKOnly, "Trial Period Expired"
        'close the workbook without saving
        ThisWorkbook.Close (False)
    End If
End Sub

I created a spreadsheet to keep track of files we process. We have an expected time we usually recieve the files by. How could I check to see if this time has already passed? I would like to highlight these values.

No luck finding an answer? You could always try Google.