From Date , need to seggregate values based on hours RRS feed

  • Question

  • Hi friends,
    I have datetime field in column M
    I will take only Hour field from there.
    After that if an hour is between 
    0-2 then "0AM-2AM"
    3-4 means "2AM-4AM"
    5-6 Means "4AM - 6 AM"
    .....Like that it follows
    the last one will be "10PM-12 AM".
    Kindly help me to get VBA Code.
    Monday, March 30, 2015 5:53 PM

All replies

  • What do you want to do with that value?

    With a formula (you can fill in the .....):

    =CHOOSE(INT(`,"0AM-2AM","2AM-4AM","4AM-6AM","6AM-8AM",........,"10PM-12 AM")

    And this, which will show 12AM and not 0AM (which is not a valid time)

        Dim rngC As Range
        For Each rngC In Range("M2:M10")
        MsgBox Format(Int(Hour(rngC.Value) / 2) / 12, "hAM/PM") & "-" & Format(Int(Hour(rngC.Value) / 2 + 1) / 12, "hAM/PM")

    Tuesday, March 31, 2015 2:43 PM