We help IT Professionals succeed at work.

time difference

pg1533
pg1533 asked
on
Medium Priority
299 Views
Last Modified: 2012-05-11
Hi,

I have one column which has different times. If the time is between than 07:00 AM - 07:00 PM then the column B should show as "Peak Minutes". I tried using if condition but it is not working. Please help me.

I have attached the excel for your reference

Regards,
Prashanth
Book4.xlsx
Comment
Watch Question

CERTIFIED EXPERT

Commented:
=IF(AND(HOUR(A1)>=7,HOUR(A1)<=18),"Peak Minutes","")
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Your data is not real date/times, it's text, so in B2:
=IF(AND(HOUR(TRIM(REPLACE(RIGHT(A2,7),6,2," "&RIGHT(A2,2)))+0)>=7,HOUR(TRIM(REPLACE(RIGHT(A2,7),6,2," "&RIGHT(A2,2)))+0)<=19),"Peak minutes","")

and copy down.
Please note that you don't have a space before AM and PM. I have inserted that and then used this formula.

=IF(AND(TIMEVALUE(TRIM(RIGHT(A2,8)))>TIMEVALUE("7:00:00 AM"),TIMEVALUE(TRIM(RIGHT(A2,8)))<TIMEVALUE("7:00:00 PM")),"Peak Hours")

Sample Attached.

Sid
Time.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Slow fingers :|

Sid
when you put this code in a method section in the VBA editor
Function process_date(value As String) As String
    
    process_date = ""
    If TimeValue(CDate(value)) > TimeValue("07:00:00") And TimeValue(CDate(value)) < TimeValue("19:00:00") Then
        process_date = "Peak Minutes"
    End If
    
End Function

Open in new window


you can use it in a excell cell formula :
e.g. cell B2 :   =process_date(A2)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.