• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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
0
pg1533
Asked:
pg1533
1 Solution
 
Nico BontenbalCommented:
=IF(AND(HOUR(A1)>=7,HOUR(A1)<=18),"Peak Minutes","")
0
 
Rory ArchibaldCommented:
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.
0
 
SiddharthRoutCommented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
See attached version of your file.
Book4.xlsx
0
 
SiddharthRoutCommented:
Slow fingers :|

Sid
0
 
Arno KosterCommented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now