Extract AM/PM as text from date/time?

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
350
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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
I tried that already, chief. It's the whole point of my post. That only returns a part of the number.
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top