Excel Catagorise AM or PM

Discussion in 'Microsoft Office' started by Dineshshah, Aug 10, 2018.

  1. Dineshshah

    Dineshshah

    Joined:
    Aug 10, 2018
    Likes Received:
    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?
     
    Dineshshah, Aug 10, 2018
    #1
    1. Advertisements

  2. Dineshshah

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,399
    Location:
    Manchester
    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, Aug 11, 2018
    #2
    Abraham Andres Luna likes this.
    1. Advertisements

  3. Dineshshah

    Dineshshah

    Joined:
    Aug 10, 2018
    Likes Received:
    1
    Thank You. But It doesnt work for all the cases. the value after 12:30 are also shown as AM.
     
    Dineshshah, Aug 12, 2018
    #3
  4. Dineshshah

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,399
    Location:
    Manchester
    I've tested it and it works fine for me, could you take a screenshot?
     
    Becky, Aug 13, 2018
    #4
  5. Dineshshah

    Dineshshah

    Joined:
    Aug 10, 2018
    Likes Received:
    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.
     
    Dineshshah, Aug 13, 2018
    #5
  6. Dineshshah

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,399
    Location:
    Manchester
    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, Aug 13, 2018
    #6
  7. Dineshshah

    Dineshshah

    Joined:
    Aug 10, 2018
    Likes Received:
    1
    Thank you very much. IT works perfectly.
     
    Dineshshah, Aug 15, 2018
    #7
    Becky likes this.
  8. Dineshshah

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,399
    Location:
    Manchester
    Glad to hear it, thanks for letting me know :)
     
    Becky, Aug 15, 2018
    #8
    1. 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 (here). After that, you can post your question and our members will help you out.