Excel Catagorise AM or PM

[Dineshshah]

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]

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!

 

[Dineshshah]

Thank You. But It doesnt work for all the cases. the value after 12:30 are also shown as AM.

 

[Becky]

I've tested it and it works fine for me, could you take a screenshot?

 

[Dineshshah]

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]

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")

 

[Dineshshah]

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")

Thank you very much. IT works perfectly.

 

[Becky]

Glad to hear it, thanks for letting me know