Hello all, I am trying to get just the month from the date a document was created and put it into a seperate column called MONTH. For example, ifa document is created on 6-29-2008, I want to be able to just show the 6 from the date in the MONTH column. I came up with a formula that suits what I am looking for somewhat, =MONTH("Name of column with thedate"), which I implement by creating a new column and using the "Calculated (calculation based on other columns)" option. However, the only problem with this formulais thatonce a new form/document is submitted into the library, the month for some reason displays 12 in the MONTH column no matter when the date the document was created on is. So ifthe document was created and submittedon 7-07-2008, even though the formula calls for a 7 to be displayed in the MONTH column, a 12 is displayed. Does anybody know the reasoning for this? Any insight at allis very much appreciated. PLEASE HELP! Thanks so much, Chris

Give this a try: =TEXT(Created,"mm") John SharePoint911: SharePoint Consulting http://www.rossonmoss.com

Hey John, Thanks for the response! I used your suggestion and it worked fine just like the other formula on documents that were already within the list. However, when I added a new form/document the row appeared something like this: CreatedMonth(Column based off your formula) 7/7/2008 4:42 PM 12 I also tried using another formula I had dug up on the internet- (=YEAR(Modified)&"-"&IF(MONTH(Modified)<10,0&MONTH(Modified),MONTH(Modified)) and heres what displayed when I added the new form/document: CreatedMonth2(based off the formula found off internet) 7/7/2008 4:42 PM 1899-12 Do you have any ideas as to why this is possibly occuring?

What is displayed if you just do a calculate field that says =[Created]? John SharePoint911: SharePoint Consulting http://www.rossonmoss.com

When I try that formula, SharePoint spits out a row that looks like this: Created Month 7/7/2008 4:54 PM12/30/1899 Very bizarre...Any ideas?

What is the exact name of the column you are referring to in the Month column? John SharePoint911: SharePoint Consulting http://www.rossonmoss.com

The exact name I'm referring to in the Month column is "Created", which is a default column created automatically by SharePoint once the form is submitted via InfoPath Services.

Ahhh...didn't realize this was getting created by InfoPath. That date you are referring to us the default date that is applied when no date is applied. I've never actually paid attention to the created on date when you create a document in InfoPath and write it to a SP list. But I'd guess that's your problem. You probably need to write the Created date to the SP List. John SharePoint911: SharePoint Consulting http://www.rossonmoss.com

Thanks so much for all your help John. Do you know how I might go about doing that? More specifically,do you know howtoadd the current date/time to a new column? Thanks again for everything, Chris

The formula for the current date and time is: [Today] John SharePoint911: SharePoint Consulting http://www.rossonmoss.com

Hey John, Implemented the =[Today] formula and the problem still exists. The date for"=[Today]"comes up as 12/30/1899... Very annoying....

Create a new list in SP -- do just a regular custom list. Create a calculated site column in that list and set it to =[Today]. It should display today's date correctly. I think what you need to do is set today's value in the InfoPath form and then write that value over to the SharePoint list. Have you tried something like that? John SharePoint911: SharePoint Consulting http://www.rossonmoss.com

Hey John, I actually just seemed to figure out a solution after messing around with it for a while. Thankfully! When creating a new column,instead of using the "Today" formula by means of the "Calculated (calculation based on other columns)" option, I used the default option "Date and Time"already provided within MOSS 2007. This seems to work rather thantyping in the [Today] formula as I kept having the same problem when I tried the formula. Very relieved the issue is resolved though. Thank you so much for all your prompt feedback and useful advice. Ihope to speak with you againon this forum. Thanks so much! -Chris

Hi,Had that exact problem yesterday, apparently it's caused by the content type and the "used in" of the calculated field. I had to get all my content type updated and ready in the list before creating the field, or else the field created will not work for the content type added after that.Regards,Dennis

Hi,I have the same problem!!!!!Someone has the solution?????Many thanks

The problem with the 1899 field is a tough one to explain. If you are creating a calculated column based on the Created or Modified field the values often come up as 1899 or 1900 instead of the correct year. It's a bug and this is a hack to get around the problem. Examples of where the issue comes up: I want to show the Month a document was created. I create a new calculated column with this formula =CHOOSE(MONTH(Created),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") I want to show the Year a document was created. I create a new calculated column with this formula =TEXT(YEAR(Created),"0000") and then the same concept except with the Modified field. For the Created field If you have this problem with the Created field, the solution is simple. All you need to do is create a workflow that sets a random text field (e.g. "T") to a random value (e.g. "1") which is triggered as soon as the item is created. This modifies the item and fixes the calculated value. For some bizarre reason it doesn't fix the Modified field. For the Modified field If you have this problem with the Modified field, its not so simple. The solution takes a few steps: 1) Create a random text field (e.g. "T") 2) Create a workflow which is triggered when the item is modified. Get it to put the Modified date into the text field "T". It will appear in the full date and time format so it needs to be trimmed. 3) And If you only need the year, create a calculated column (e.g. ModY) with this formula. =RIGHT((LEFT(T,LEN(T)-10)),LEN(LEFT(T,LEN(T)-10))-6) And TADA! It works. You have the year it was modified.If this isn't exactly what you asked for, it might just get you close. Good luck. I was REALLY pleased when I worked this out.

This definitely works: =IF([Column]="","",[Column])

use =Month(Created)