Excel Catagorise AM or PM

D

Dineshshah

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?
 
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! :)
 
I've tested it and it works fine for me, could you take a screenshot?
 
upload_2018-8-13_19-28-48.webp

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.
 
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")
 
Back
Top