SharePoint List formula to calculate Month and quarter from date value, where Date is the column name of type "Date and Time"
1. Year
Formula :
=YEAR(Date)
2. Date
Formula:
=DAY(Date)
3. Month
Formula :
=IF(MONTH(Date)=1,"January","")&
IF(MONTH(Date)=2,"February","")&
IF(MONTH(Date)=3,"MARCH","")&
IF(MONTH(Date)=4,"April","")&
IF(MONTH(Date)=5,"May","")&
IF(MONTH(Date)=6,"June","")&
IF(MONTH(Date)=7,"July","")&I
F(MONTH(Date)=8,"August","")&
IF(MONTH(Date)=9,"September","")&
IF(MONTH(Date)=10,"October","")&
IF(MONTH(Date)=11,"November","")&
IF(MONTH(Date)=12,"December","")
4. Quarter
Formula:
=IF(MONTH(Date)<=3,"Q1", IF(MONTH(Date)<=6,"Q2", IF(MONTH(Date)<=9,"Q3",IF(MONTH(Date)<=12,"Q4",""))))
5. Getting Moday t0 friday date. If selects any date in the week, the calculated column fills with monday to to friday date, i.e. if selects "3/9/2009" then the calculaed field fills with "2009/03/09 - 2009/03/13".
=TEXT([Select Week]+(2-WEEKDAY([Select Week])),"YYYY/MM/DD")&" - "&TEXT([Select Week]+(6-WEEKDAY([Select Week])),"YYYY/MM/DD")
Where "Select Week" is the name of date filed. If you want any other day change the week day values 2(monday) and 6(friday)
Subscribe to:
Post Comments (Atom)
7 comments:
What version of sharepoint are you using? I get syntax error messages
Another way to get quarters would be
1) For the quarter number as an integer
=INT((MONTH(Date)-1)/3)+1
2) For the First date of the quarter
=DATE(YEAR(Date),(INT((MONTH(Date)-1)/3)*3)+1,1)
To get the last date in the quarter
=DATE(YEAR(Date),(INT((MONTH(Date)-1)/3)*3)+4,1)-1
I needed that. Thank you.
You need to replace "date" with the name of your date field. You also need to add brackets [] around the field name if there is a space in the name.
Thanks for the info. I've the following formula working to display "Q# YYYY" in a calculated field based on a date in another column called in my case "Date of Completion".
The whole formula looks like this
=IF(ISBLANK([Date of Completion]),"",(CONCATENATE("Q"&(INT((MONTH([Date of Completion])-1)/3)+1)&" "&(YEAR([Date of Completion])))))
and as Anonymous said "You also need to add brackets [] around the field name if there is a space in the name"
HI,
I applied the formula for calculating month by changing "Date" with my date field name.
Its throwing syntax error.
NOte: Am using sharepoint foundation 2010.
what could be the reason???
Thanks
Ganesh
It's a lot easier to calculate the month like this:
=TEXT([Date], "MMMM")
Post a Comment