• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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