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?
 

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,346
Reaction score
1,473
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! :)
 
Joined
Aug 10, 2018
Messages
4
Reaction score
1
Thank You. But It doesnt work for all the cases. the value after 12:30 are also shown as AM.
 

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,346
Reaction score
1,473
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
Administrator
Joined
Mar 25, 2003
Messages
7,346
Reaction score
1,473
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")
 

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,346
Reaction score
1,473
Glad to hear it, thanks for letting me know :)
 

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