Friday, August 7, 2009

Calculated Fields Formula : Date Time

Scenario:
I think this is one of the most common topic dealing with date differences and finding week of the day etc. using sharepoint calculated fields.

Some Examples:

1. Difference between two dates ( in x days , y hours , z minutes format )

=IF(HOUR(date2)>HOUR(date1),DATEDIF(date1,date2,"d")&" days "&HOUR(date2-date1)&" hours ",(DATEDIF(date1,date2,"d")-1)&" days "&HOUR(date2-date1)&" hours ")&MINUTE(date2-date1)&" minutes"
2. Finding the week start date ( last monday ). You can use this to filter items for a particular week.
=CHOOSE(WEEKDAY([Today]),[Today]-6,[Today],[Today]-1,[Today]-2,[Today]-3,[Today]-4,[Today]-5)
MSDN

2 comments:

Mohamed Hachem August 20, 2009 at 10:51 AM  

Yo man that formula sounds so complex I give ya simple solution
:) Just use this

INT(([columnName])*1440)

works like magic

Sandeep K Nahta August 20, 2009 at 1:05 PM  

But having 5000 minutes in column value will not make sense to user.. it shud be x days , y hrs and z minutes..