[zt]SharePoint Calculate date column 日期计算列(20100916 23:04:14)

SharePoint Calculate date column -日期计算列

(2010-09-16 23:04:14)

转载

标签： it

之前做过SharePoint list 日期计算列，不过忘了将公式记录下来，当重新用到的时候花了好大力气来找，特此记录

 

根据日期字段可以求当前周，当前月，当前日

 

Activity Date 为日期，则

 

Day  =TEXT(WEEKDAY([Activity Date]),"ddd")   //星期几

Month  = TEXT([Activity Date],“MMM”)  //月份

Week =INT(([Start Date]-DATE(YEAR([Start Date]),1,1) (TEXT(WEEKDAY(DATE(YEAR([Start Date]),1,1)),"d")))/7) 1   //第几周

 

还有一些其他的，慢慢研究，先专贴出来分享

Get Week of the year
=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])) 0.5-WEEKDAY(DATE(YEAR([Start Time]),MONTH([Start

Time]),DAY([Start Time])),2) 1

First day of the week for a given date:
=[Start Date]-WEEKDAY([Start Date]) 1

Last day of the week for a given date:
=[End Date] 7-WEEKDAY([End Date])

First day of the month for a given date:
=DATEVALUE(“1/”&MONTH([Start Date])&”/”&YEAR([Start Date]))

Last day of the month for a given year (does not handle Feb 29). Result is in date format:
=DATEVALUE (CHOOSE(MONTH([End Date]),31,28,31,30,31,30,31,31,30,31,30,31) &”/” & MONTH([End Date])&”/”&YEAR([End

Date]))
Day Name of the week : e.g Monday, Mon
=TEXT(WEEKDAY([Start Date]), “dddd”)
=TEXT(WEEKDAY([Start Date]), “ddd”)

The name of the month for a given date – numbered for sorting – e.g. 01. January:
=CHOOSE(MONTH([Date Created]),”01. January”, “02. February”, “03. March”, “04. April”, “05. May” , “06. June” , “07. July” ,

“08. August” , “09. September” , “10. October” , “11. November” , “12. December”)

Get Hours difference between two Date-Time :
=IF(NOT(ISBLANK([End Time])),([End Time]-[Start Time])*24,0)

Date Difference in days – Hours – Min format : e.g 4days 5hours 10min :
=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created) IF(AND(MONTH(Today)
<=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
>=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months,
“&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”