VBA get time

Vanda_a

Well-known Member
Joined
Oct 29, 2012
Messages
923
Hello all

could you guys advise my below code please
Code:
dim c as range

For each c in range("D2:D100")
   If Time(hour(c)),minute(c))) > 9:30 then
   msgbox c & "is bigger"
   End if
next c

D2:D100 is date and time(ex: 11/08/2015 8:30:22)
How to use Time properly?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
I think you're after something like this:

Code:
For Each c In Range("D2:D100")
   If TimeSerial(Hour(c), Minute(c)) > #9:30:00 AM# Then
    MsgBox c.Address & " is bigger"
   End If
Next c
 

Vanda_a

Well-known Member
Joined
Oct 29, 2012
Messages
923
I think you're after something like this:

Code:
For Each c In Range("D2:D100")
   If TimeSerial(Hour(c), Minute(c)) > #9:30:00 AM# Then
    MsgBox c.Address & " is bigger"
   End If
Next c

I got it working now. Thank you very much
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
I think you're after something like this:

Code:
For Each c In Range("D2:D100")
   If TimeSerial(Hour(c), Minute(c)) > #9:30:00 AM# Then
    MsgBox c.Address & " is bigger"
   End If
Next c
Some may find the syntax odd, but this would also work...
Code:
For N = 1 To 99
  If Evaluate("MOD(D2:D100,1)>Time(9,30,0)")(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next
 

Vanda_a

Well-known Member
Joined
Oct 29, 2012
Messages
923

ADVERTISEMENT

Some may find the syntax odd, but this would also work...
Code:
For N = 1 To 99
  If Evaluate("MOD(D2:D100,1)>Time(9,30,0)")(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next
Thanks for the alternative.
One more question pls. Dim FP as ....? FP = TimeSerial(hour(c),minute(c),second(c)) or set FP = ....
i would like to have a shortcut.
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Some may find the syntax odd, but this would also work...
Code:
For N = 1 To 99
  If Evaluate("MOD(D2:D100,1)>Time(9,30,0)")(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next

nice :) or you could run EVAL one time and then loop through the array..
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857

ADVERTISEMENT

Thanks for the alternative.
One more question pls. Dim FP as ....? FP = TimeSerial(hour(c),minute(c),second(c)) or set FP = ....
i would like to have a shortcut.

Code:
Dim FP as Date

Code:
FP = TimeSerial(hour(c),minute(c),second(c))

or
Code:
FP = c.Value - Int(c.Value)
 

Vanda_a

Well-known Member
Joined
Oct 29, 2012
Messages
923
Code:
Dim FP as Date

Code:
FP = TimeSerial(hour(c),minute(c),second(c))

or
Code:
FP = c.Value - Int(c.Value)

Thank you very much

Below code. Msgbox = 1.11212e...... Why it doesn't show time? FP = 9:00 AM then it should pop up 8:44 AM
MsgBox FP - TimeSerial(Hour(c), Minute(c) - 16, Second(c))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
nice :) or you could run EVAL one time and then loop through the array..
Good point... it was almost 6am local time when I wrote that, just before I went to sleep for the "night", so I guess I wasn't thinking clearly at the time, but yes, you are right, there no need to repeat the Evaluation inside the loop.
Code:
Dim N As Long, TimeArray As Variant
......
......
TimeArray = Evaluate("MOD(D2:D100,1)>Time(9,30,0)")
For N = 1 To 99
  If TimeArray(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,027
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top