Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    Or, for the fractional part, =MOD(date_time,1)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    Not sure if your questions were really answered completely, so I will expand on the answers already given.

    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. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •