Excel Catagorise AM or PM


Joined
Aug 10, 2018
Messages
4
Reaction score
1
Hi,
I have date strings as 2017-04-03 09:14.
I need to have two catagories AM or PM Shifts of working. But the situation is AM should be upto 12:30 and PM is after 12:30 to rest of the time. Two different person works in this time frame with two different wages, So I cannot overlap the period of half an hour to next. The solution =IF(MOD(A2,1)>0.5,"PM","AM") only address for time frame 12:00 am. whats the formula to divide 12:30 as AM and rest as PM? How can I get the solution to my problem?
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
I don't know of a way to do this with custom formatting, but it should be possible with a formula. Try this:

=IF(AND(HOUR(A1)>=12,MINUTE(A1)>=30),"PM","AM")

Hope this helps! :)
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
I've tested it and it works fine for me, could you take a screenshot?
 
Joined
Aug 10, 2018
Messages
4
Reaction score
1
upload_2018-8-13_19-28-48.png

The first column AM PM is the one I had done before and the last column is done by the way you have provided.
Thank You.
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Oh whoops, I made a logic error with the formula. Apologies! It should be this instead:

=IF(TIME(HOUR(A1),MINUTE(A1),0)>TIME(12,30,0),"PM","AM")
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top