Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Extract AM/PM as text from date/time?

  1. #1
    Board Regular Enigmachrysalis's Avatar
    Join Date
    Apr 2009
    Location
    Columbus, Ohio
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract AM/PM as text from date/time?

    I have a column of times, that are actually dates of 1/0/1900 plus time, from which I want to extract the AM or PM status. I can format it to show this, but it's really just a fraction of a day. I need the actual text "AM" or "PM", not a decimal value.

    Is there a formula to do this?
    Help me MVPs, you're my only hope!

  2. #2
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    Quote Originally Posted by Enigmachrysalis View Post
    I have a column of times, that are actually dates of 1/0/1900 plus time, from which I want to extract the AM or PM status.
    Could you post an example of the data you are working with? Basically to show how your input is currently formatted.
    Kristy

  3. #3
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    If your time is in A2 try

    =IF(A2<0.5,"AM","PM")

  4. #4
    Board Regular Enigmachrysalis's Avatar
    Join Date
    Apr 2009
    Location
    Columbus, Ohio
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    It is a formula that generates a time in this format - 4:20:13 PM

    The actual date would read 1/0/1900 4:20:13 PM if formatted to show this part of the field. I did this so that I could compare all the times equally w/out regard to date.

    However, when I try to extract the PM or AM from this time, i get a number since the actual data is simply a decimal, the data as a fraction of the day.

    I wasnt to get the PM as text from 4:20:13 PM, so that I can use that as a filed in a pivot table.
    Help me MVPs, you're my only hope!

  5. #5
    Board Regular
    Join Date
    Sep 2008
    Posts
    505
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

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

  6. #6
    Board Regular Enigmachrysalis's Avatar
    Join Date
    Apr 2009
    Location
    Columbus, Ohio
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    That just give me a #VALUE error.
    Help me MVPs, you're my only hope!

  7. #7
    Board Regular
    Join Date
    Sep 2008
    Posts
    505
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    Try this: =RIGHT(A2,2)

  8. #8
    Board Regular Enigmachrysalis's Avatar
    Join Date
    Apr 2009
    Location
    Columbus, Ohio
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    I tried that already, chief. It's the whole point of my post. That only returns a part of the number.
    Last edited by Enigmachrysalis; Oct 19th, 2010 at 05:00 PM.
    Help me MVPs, you're my only hope!

  9. #9
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    I am confused.

    You state MOD returns #VALUE! which implies that the cell being evaluated does not contain a number

    You state that retrieving the two rightmost chars from the cell return part of the time decimal (as opposed to AM/PM) thus implying cell contains a number rather than string.

    We would expect either/or

    Without trying to be "amusing" - when you applied the MOD did you apply to the correct cell ?
    Does my a$$ look big in this picture ?

  10. #10
    Board Regular Enigmachrysalis's Avatar
    Join Date
    Apr 2009
    Location
    Columbus, Ohio
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract AM/PM as text from date/time?

    Well, it's definitely a number in the cell, but I must have made some error yesterday, b/s something I was trying myself yesterday began to work.

    I wouldn't have found the solution w/out all your guys help!
    Help me MVPs, you're my only hope!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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