# Thread: How 2 get countof AMs and PMs of date column (Excel)

1. ## How 2 get countof AMs and PMs of date column (Excel)

I have a column with dates/times like; 4/2/2003 9:48:00 AM
At the bottom I want a count of those that are AM and those that are PM.
I tried an array formual looking for the "AM" and "PM" respectively but I guess the date really being a number value, this didn't work.
(I don't want to convert the dates to text. uggg)

Can someone help me with a formula for this? Also, I'd like to use conditional formating to highlight the AM dates. This may be the same solution, if not, need help on this one too.
Thanks a bunch!
Bruce

2. ## Re: How 2 get countof AMs and PMs of date column (Excel)

Date/Times are represented as days since some reference date (1/1/1900, I think). The time part is just a fractional date so any fractional part <.5 is AM and >=.5 is PM. To get the fractional part, use =A1-INT(A1)

3. ## Re: How 2 get countof AMs and PMs of date column (Excel)

Or, for the fractional part, =MOD(date_time,1)

4. ## Re: How 2 get countof AMs and PMs of date column (Excel)

Select the range of dates (I will assume A1:A100)
format - cond form
formula is:
=MOD(A1,1)<0.5
<format>
Patterns-tab [choose a color]
<ok><ok>

Now AM is highlighted

Put this formula in a cell where you want the count of AM. It is an ARRAY (confirm with ctrl-shift-enter)
=SUM(IF(MOD(\$A\$1:\$A\$100,1)<=0.5,1))

Put this formula in a cell where you want the count of PM. It is also an ARRAY (confirm with ctrl-shift-enter)
=SUM(IF(MOD(\$A\$1:\$A\$100,1)>0.5,1))

Change the ranges as appropriate
Steve

5. ## Re: How 2 get countof AMs and PMs of date column (Excel)

Exactly what I needed. Thanks a bunch!!
bruce

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•