Formula needed..Or Suggestion Pivot..

[VBABEGINER]

In Col A, I have time..

6/14/2017 6:22:23 AM

In Col B, I use Hour formula to get Hour..

=HOUR(A2)

I got the answer.."6"
...................................
Then I need AM & PM in Value..So modified formula like below..
Col C new formula

=IF(MOD(A2,1)>0.5,"PM","AM")

Col B then modified..

=HOUR(A2) &" " &C2

I got the answer.."6 AM"
---------------------------

I make a pivot of this. In pivot, dates are NOT ordered in 0 AM, 1 AM, 2 AM,....and so on..(In Row label)*2nd edit

Therefore, I do Sort AtoZ, but now values display like....0 AM, 1 AM, 10 AM, 11 AM..

Which incorrect...

Can any please guide me how do I this pivot Or any new formula which directly gives me time values correctly..

 

[arthurbr]

Have you tried formatting your time as AM/PM instead of creating a text string?

 

[VBABEGINER]

Hi, it is already in this format in Col A. what you said.

Have you tried formatting your time as AM/PM instead of creating a text string?

Actually, prev i use, only hour formula...and it is coming in pivot is ascending order only, i.e. 0, 1, 2, ....like

but i got request change now, saying that, in pivot do some trick where instead showing 0, 1, 2,...in graph...it will show as, 00 AM, 01 AM, ...13 PM, 14 Pm.. This is I want...to see in graph...

 

[arthurbr]

You can format the time field as required in the PT