Thanks:  0
Likes:  0

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

1. ## 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?

2. ## Re: Extract AM/PM as text from date/time?

Originally Posted by Enigmachrysalis
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.

3. ## Re: Extract AM/PM as text from date/time?

If your time is in A2 try

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

4. ## 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.

5. ## Re: Extract AM/PM as text from date/time?

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

6. ## Re: Extract AM/PM as text from date/time?

That just give me a #VALUE error.

7. ## Re: Extract AM/PM as text from date/time?

Try this: =RIGHT(A2,2)

8. ## 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.

9. ## 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 ?

10. ## 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!

## User Tag List

#### Posting Permissions

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