How to check if the time is lesser than 14 hours [SOLVED]

[ThrottleWorks]

Hi,


I have column AR, values in the column are time such as 6:40:24 AM.


In column AS, I need to populate value "Yes" if the time is lesser than 14 hours.


I am trying to this by following code, but it is not working.

Can anyone help me in this please.

[pre]

Dim rng20 As Range
Set rng20 = Range("ar2:ar" & endrow)

For Each rn In rng20
If rn.Value < Hour(14) Then
rn.Offset(0, 1).Value = "Yes"
Else
rn.Offset(0, 1).Value = ""
End If
Next rn

[/pre]

 

[NARAYANK991]

Hi Sachin ,


It is time you improved your debugging technique.


Place the cursor in the code , and press F8 ; keep stepping through the code by pressing F8 , till you come to the line :

[pre]

If rn.Value < Hour(14) Then
Alternatively , place the cursor on the above line , and press F9 to put a breakpoint at that line. Press F5 to start execution of the code ; Excel will pause execution when it comes to a line where a breakpoint has been placed.


In either of the above cases , put the following in your Immediate Window :

?rn.Value

[/pre]
See what is displayed ; this will tell you what you need to compare the value with.


Narayan

 

[ThrottleWorks]

Hi Narayan Sir,


Thanks a lot for the help.


I am trying this.

 

[Luke M]

What Narayan has posted is good debugging method, but I think I can already see the problem. In your line:

rn.Value < Hour(14) Then

The Hour method takes a time value and gives you the hour. The integer 14 represents Jan 14, 1900 0:00:00 AM, thus the value of [code]Hour(14) will be 0. I'm guessing that's not what you are wanting to compare to, otherwise you would just test [code]< 0

. If the rn[/code] contains a time, I think it should be written:

Hour(rn.Value) < 14 Then[/code]

 

[ThrottleWorks]

Luke Sir, yes you are right.


This is working.


I was not able to understand "The integer 14 represents Jan 14, 1900 0:00:00 AM" but not the time.


Luke Sir, Narayan Sir, thank a lot for the help.

 

[Luke M]

Hi ThrottleWorks

In XL, numbers/dates/time are all really stored as the same thing, a numerical value. In XL, the number 1 can also represent a date. XL uses the 1900 computer date system, so the 1 represents Jan 1, 1900. Similarly, the numerical value of 1/24 = 1 hour (1 day divided by 24). You can see this most easily by inputting dates and times into cells and then changing the cell format to number with decimal.

So, quick summary, a integer which has no decimals, and XL stores time values by using decimals, so the number 14 is the 14th day in the 1900 date system, no time. aka,

January 14, 1900 0:00:00

 

[ThrottleWorks]

Luke Sir, thanks for the guidence, sorry for late reply.


Have a nice day ahead.