Find the month based on a column


Badge +11

I have a date/time column in SharePoint 2013 list. I'd like to use Nintex to determine the month of that date/time column. I will have to filter that list later based on the recent month items. So I would need to be able to pull items that are dated for April. Next month, I'd have to pull items for May, etc.


16 replies

Userlevel 6
Badge +16

I'd add a new column on the SharePoint list with a formula to get the month

Badge +11

Yeah, I figured I'd add a column but I didn't know what the formula would be to get the month.

Badge +8

burkslm​ Assuming you've got a SharePoint list date column named 'TestDate', these formulas used in calculated columns will give you what you need:

  • =TEXT([TestDate],"mm")
    • Returns 01 if date is 12-Jan-2015
  • =TEXT([TestDate],"mmmm")
    • Returns January if date is 12-Jan-2015
  • =TEXT([TestDate],"mmm")
    • Returns Jan if date is 12-Jan-2015
Badge +11

I have a date column that is in the format of 4/13/2016. Will your solution still work?

Badge +11

And I need it to be in the datatype of a number not text. Because I will need to compare using <>=

Badge +8

Yes this format won't be a problem – it's the format I tested against:

Screen Shot 2016-04-13 at 2.20.49 PM.png

Badge +8

If you're need the month number (int), just use the first of the three formulas I posted : =TEXT([TestDate],"mm"). This will return '04' for 4/14/2016.

Badge +11

I want to return 4 not April.

Badge +11

I hate to be a pain, but I don't want to have leading 0s. I'm going to have to do comparisons. I was getting an error when I did what you posted.

Badge +8

Regarding the datatype, you can configure this on the creation of a calculated column. Do so by specifying 'number' when asked which data type you'd like returned from your formula (last property).

Badge +11

Ok - it was my fault. Yes, I specified number but I made a mistake and forgot the first parentheses. And instead of "mm", I did "m". Thanks so much!

Badge +4

How would you return "April, 2016" (i.e. not just the month as full text, but also the year)?

Badge +11

Create a new question

Badge +8

For your needs Drew Abas​ – You'd need the following formula (assuming you've got a date-time column named TestDate):

  • =TEXT(TestDate," MMMM, yyyy")

Also, don't forget to set your data-type to "Date and Time Format > Date Only."

Badge +4

Works like a charm! Thanks!

Badge +8

no problem – glad that worked for you!

Reply