VBA - Remove Seconds from NOW function

Question

I have something that notify me an hour before it happens. For that, I use the NOW function in VBA as I need it to check for the Date as well.

The problem is the script runs every 20 seconds so I can't have it consider seconds for the NOW function.

Is there a way to remove those? To have only like (DAY,MONTH,YEAR,HOUR,MINUTE)?

Something along those lines:

 MyLimit = NOW(DAY,MONTH,YEAR,HOUR,MINUTE)

 For Each FormulaCell In FormulaRange.Cells
 With FormulaCell
            If .Value = MyLimit Then
            Call Notify

Here is the script in which I attempt to detect the date and time.

Option Explicit

Public Function AutoRun()
Application.OnTime Now + TimeValue("00:00:20"), "TaskTracker2"
End Function

Public Sub TaskTracker2()
Dim FormulaCell          As Range
Dim FormulaRange    As Range
Dim NotSentMsg      As String
Dim MyMsg           As String
Dim SentMsg         As String
Dim SendTo          As String
Dim CCTo            As String
Dim BCCTo           As String
Dim MyLimit         As Date


NotSentMsg = "Not Sent"
SentMsg = "Sent"
SendTo = Range("D2")
CCTo = Range("E2")
BCCTo = Range("F2")

MyLimit = Format((Now), "DD/MM/YYYY HH:MM")

Set FormulaRange = Range("E5:E35")
On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
    With FormulaCell
            If .Value = MyLimit Then
                MyMsg = SentMsg
                If .Offset(0, 1).Value = NotSentMsg Then
                    strTO = SendTo
                    strCC = CCTo
                    strBCC = BCCTo
                    strSub = "[Task Manager] Reminder that you need to: " & Cells(FormulaCell.Row, "A").Value
                    strBody = "Hello Sir, " & vbNewLine & vbNewLine & _
                        "This email is to notify that you that your task : " & Cells(FormulaCell.Row, "A").Value & " with the following note: " & Cells(FormulaCell.Row, "B").Value & " is nearing its Due Date." & vbNewLine & "It would be wise to complete this task before it expires!" & _
                        vbNewLine & vbNewLine & "Truly yours," & vbNewLine & "Task Manager"
                    If sendMail(strTO, strSub, strBody, strCC) = True Then MyMsg = SentMsg

                End If
            Else
                MyMsg = NotSentMsg
            End If
        Application.EnableEvents = False
        .Offset(0, 1).Value = MyMsg
        Application.EnableEvents = True

    End With

Next FormulaCell
AutoRun

ExitMacro:
Exit Sub

EndMacro:
Application.EnableEvents = True

MsgBox "Some Error occurred." _
     & vbLf & Err.Number _
     & vbLf & Err.Description

End Sub

score 5 · Answer 1 · answered Feb 04 '16 at 21:16

5

To strip the seconds off Now, you can use some maths or to-and-from text conversion.

CDate(format(Now, "dd-mmm-yyyy hh:mm"))
'... or,
CLng(Now * 1440)/1440

Both of those return a true, numerical datetime value with the seconds stripped off. They do not average the seconds to the nearest minute; simply remove them.

answered Feb 04 '16 at 21:16

1

I believe CLng does round (and rounds to the nearest even number) – Ron Rosenfeld Feb 04 '16 at 21:33
You are indeed correct; I must have confused the results from several methods I tried. – Feb 04 '16 at 21:37
Thanks a lot Jeeped, looks like this works too :) I'll go with Ron's solution as he posted a little quicker and his works great too! It's really appreciated! – Francis Maltais Feb 04 '16 at 21:51
@FrancisMaltais - No worries; there are a number of good ideas here. I would however question your definition of 'quicker' since my post was seven minutes prior to Ron's. – Feb 04 '16 at 21:57
Ohh that's true, I misread the times. My bad D: Can I choose 2 best answers? – Francis Maltais Feb 04 '16 at 21:59
@FrancisMaltais - Don't worry about it. Often times identical results are produced by very different methods. Choose the one that makes sense to you. – Feb 04 '16 at 22:01

score 4 · Accepted Answer · answered Feb 04 '16 at 21:22

4

You could just round MyLimit to the nearest minute:

MyLimit = Round(Now * 1440, 0) / 1440

Consider, when comparing it to the contents of a cell, that you might need to use a <= or >= comparison to avoid problems if the time changes at the "wrong" time for an equality to hold true.

answered Feb 04 '16 at 21:22

Ron Rosenfeld

53,870
7
28
60

That worked Magic! Thanks I went through so many possibilities! – Francis Maltais Feb 04 '16 at 21:47
If I wanted to get the notification like 30 minutes before NOW, how would I do so? I'm not really good at writing multiple modifications of a function :P – Francis Maltais Feb 04 '16 at 21:48
1

@FrancisMaltais Just add 30 minutes to `MyLimit` before you do the comparison. 30 minutes = 30/1440 or `Timeserial(0,30,0)` – Ron Rosenfeld Feb 04 '16 at 21:54
So something like: MyLimit = (Round(Now * 1440, 0) / 1440) - 30 / 1440 ? – Francis Maltais Feb 04 '16 at 22:07
@FrancisMaltais You could, although for clarity I would use two lines; the first to Round MyLimit, and the second to add or subtract the 30 minutes. – Ron Rosenfeld Feb 04 '16 at 22:10
Or a prettier variation: ((Round(Now * 1440, 0) - 30) / 1440) Something like MyLimit = Round(Now * 1440, 0) / 1440 & MyLimit = MyLimit - 30/1440 ?? – Francis Maltais Feb 04 '16 at 22:11

vacip · Answer 3 · 2016-02-04T21:31:38.437

3

Another method would be this:

MyLimit = now-second(now)/60/60/24

second(now) returns the seconds, and the /60/60/24 converts it to days, which every date and time is stored in. Use this or Jeeped's answer, any one of these should work.

Edit:

To avoid the tiny but existing possibility of error, use this:

MyLimit = now
MyLimit =MyLimit -second(MyLimit)/60/60/24

edited Feb 04 '16 at 21:31

answered Feb 04 '16 at 21:20

vacip

5,246
2
26
54

Thanks :) It looks like it would work too! Really appreciated. – Francis Maltais Feb 04 '16 at 21:50

ske57 · Answer 4 · 2016-02-04T21:07:50.797

0

Use the Date function instead of the NOW function https://msdn.microsoft.com/en-us/library/aa227520(v=VS.60).aspx

UPDATE

edited Feb 04 '16 at 21:07

answered Feb 04 '16 at 20:59

ske57

577
4
21

When I do so, it fails to check for the time of the day :/ – Francis Maltais Feb 04 '16 at 21:00
So if I use Date and my Reference Cell is per say 02/04/2016 - 4:02 PM, I get nothing. But it does work when only using the 02/04/2016 Date. – Francis Maltais Feb 04 '16 at 21:01
What about Format(NOW(), "dd/mm/yyyy - hh:mm AM/PM") – ske57 Feb 04 '16 at 21:04
I get a Run-time error '13' - Type Mismatch when I attempt to MyLimit = Format((Now), "DD/MM/YYYY HH:MM") – Francis Maltais Feb 04 '16 at 21:08
Furthermore, the script doesn't trigger if I remove MyLimit and do: If .Value = Format((Now), "DD/MM/YYYY HH:MM") Also, I took care to format the cells in range to a custom Format DD/MM/YYYY HH:MM – Francis Maltais Feb 04 '16 at 21:09
Dim MyLimit As Date MyLimit = Format(Now,"dd.mm.yy hh:mm AM/PM") – ske57 Feb 04 '16 at 21:13
Thanks a lot for the help so far. This modification fixes the compile error, but the script still doesn't send my notification when it should... I'll add the script below, maybe it will give you an idea. Updated in the main topic. – Francis Maltais Feb 04 '16 at 21:39

score 0 · Answer 5 · answered Feb 04 '16 at 21:04

0

Try limit = Format((Now), "DD/MM/YYYY HH:MM")

answered Feb 04 '16 at 21:04

Slubee

406
3
11

I get a Run-time error '13' - Type Mismatch when I attempt to MyLimit = Format((Now), "DD/MM/YYYY HH:MM") – Francis Maltais Feb 04 '16 at 21:10

score 0 · Answer 6 · answered Sep 19 '16 at 08:27

0

I usually just go with the function =TIME(HOUR(NOW()),MINUTE(NOW()),0)

answered Sep 19 '16 at 08:27

kitarika

175
1
12

Remko Delsman · Answer 7 · 2017-05-30T14:03:42.323

0

Alternative approach per VBA Office 2010 and later:

 Dim DateWithoutSeconds : DateWithoutSeconds = DateAdd("s",-Second(Now),Now)

Notice that the minus (-) removes the seconds.

More info at https://msdn.microsoft.com/en-us/library/office/gg251759.aspx

edited May 30 '17 at 14:03

answered May 30 '17 at 13:56

Remko Delsman

1
2

VBA - Remove Seconds from NOW function

7 Answers7