I have a document library with a calculated column. This document library is populated from a windows service. Every time a new document is added, the calculated column has a formula to choose whether it is AM or PM. My problem is when I want to filter the calculated column, it has multiple values of AM and PM.

Duplicate filter values

When I change the returned data type of the calculated column, the filter problem gets fixed (meaning one AM and one PM) but when a new document gets added, the problem comes back.

Has anybody encountered the same? Any ideas or suggestions are appreciated. Thanks.

  • Can you paste here your calculated column formula? Aug 21, 2014 at 4:28
  • Here it is: =IF(HOUR([Shift Start Date / Time])<12,"AM","PM")
    – joanna
    Aug 21, 2014 at 4:32

1 Answer 1


Your comment says your date column is called [Shift Start Date / Time], so this calculated column formula outputs AM or PM:

=TEXT([Shift Start Date / Time],"AM/PM")

I tested this on a custom list and it works for me.

SOURCE: http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.