Link to home
Start Free TrialLog in
Avatar of pg1533
pg1533

asked on

time difference

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
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image
=IF(AND(HOUR(A1)>=7,HOUR(A1)<=18),"Peak Minutes","")
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
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.
Avatar of SiddharthRout
SiddharthRout
Flag of India image
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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SiddharthRout
SiddharthRout
Flag of India image
Slow fingers :|

Sid
Avatar of Arno Koster
Arno Koster
Flag of Netherlands image
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)