Need Help on formating date/time

[digitalshaw]

I have this column of phone call data in military time
8/1/2010 3:06:01 AM

I have a client who wants to see what day and hour of the day the calls are coming in. I got the day column going, now how would I convert this data into pivot ready time, non military?

I want to show him a report by day and by hour for calls?

Thanks for anyone's help. This is a great forum of proud Excel geeks.

 

[schielrn]

Try putting the data in a pivot tbale and count the times.

Then change the format of the row labels in the table to a custom format that you would like, maybe:

mm/dd/yy h AM/PM

I think you will need a helper column to actually use that the formatted times. But the helper column could be:

=text(a1,"mm/dd/yy h AM/PM")

Then fill the formula down the column to all the data.

Hope that helps.

 

[texasalynn]

is this what you need?

Excel Workbook
	A	B
1	Call Data	Hour
2	8/1/2010 15:06	3 PM
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(HOUR(A2)>=12,HOUR(A2)-12&" PM",HOUR(A2))

 

[digitalshaw]

So close Lynn! Thank you.

What about adding AM as well. I also get 0PM for 12 PM. What do you think?

<TABLE style="WIDTH: 150pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=200><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2617859 class=xl65 height=20 width=111 align=right>7/1/2010 13:43</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=89>1 PM</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>7/1/2010 13:29</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>1 PM</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>7/1/2010 12:38</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>0 PM</TD></TR></TBODY></TABLE>

 

[barry houdini]

Try this version

=TEXT(A2,"h AM/PM")

 

[digitalshaw]

Bam! That was easy. Thanks.