We help IT Professionals succeed at work.

# time difference

on
Medium Priority
299 Views
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

## View Solution Only

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.

Commented:
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)

Commented:
Slow fingers :|

Sid

Commented:
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
``````

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