time taking 2 minutes from 05,

[smiler44]

I am trying to display a message box 2 minures before pre defined times, morning break, lunch break and afternoon break.
If the predefined time has 12 or more minutes in the time such as 08:13 then everything is fine but if the predefined time is say 08:03 to 08:11 ( not tried dealing with under :03 yet, the hours dont seem to be a problem only the minutes) my code errors as taking 2 "minutes" away for say 08:05 gives me 08:3 when I need 08:03, taking 2 minutes away from 08:11 gives me 08:9 when I need 08:09. I have pasted the code below for some advice pelase.

I have a clock that updates textbox1 with the time taken from the PC

Hope this makes sence

Sub moday()
    Dim ltime As String ' current hours and :
    Dim brktime As String ' break time
    Dim rtme As String 'break minutes
    Dim rrtime As String ' break hours
    Dim mer As String 'current hours with break minutes, minus 2 minutes
    Dim acttime As String ' current time
    'textbox1 current time
    'textbox8 when break time starts, predefined time
    
    If Sheet1.TextBox1.Value < Sheet1.TextBox8.Value Then
    acttime = Sheet1.TextBox1.Value
    
    ltime = Left(Sheet1.TextBox1.Value, 3) ' current hours and :
    
    rtme = Right(Sheet1.TextBox8.Value, 2) ' break minutes
    rrtme = Left(Sheet1.TextBox8.Value, 3)
    
    mer = rrtme & (rtme - 2) 'current hours with break minutes minus 2
    If acttime = mer Then
    MsgBox ("take break")
    End If
    End If
    end sub

smiler44

 

[ajetrumpet]

smiler,

use the dateadd() function to add or subtract minutes from the TIME instead of extracting with the string functions. that should work if you're getting the time from the system clock.

 

[smiler44]

wow Adam, that was "easy". Thank you I spent hours getting my macro together, I was quite please with it.

I have just one question, is it possible to format the funtion so it gives me the time as 00:00 instead of 00:00:00? If I could just knock off those seconds.

I have used a second line of code to format ldate so I have

Dim LDate As Date
Dim lldate As String
Dim beve As String 'minutes before before event
beve = Sheet1.TextBox32.Value 
LDate = DateAdd("n", -beve, Sheet1.TextBox1.Value) 
lldate = Format(LDate, "hh:mm")
 
Thank you again
smiler44

 

[ajetrumpet]

I have just one question, is it possible to format the funtion so it gives me the time as 00:00 instead of 00:00:00? If I could just knock off those seconds.

of course. lookup the format() function in the help menu. it shows you all of the diff formats for dates and times. manuals are there too....e.g.

 format(time(), "hh:mm:ss")