Monday, February 06, 2006

Some of my favourite date WSS List Formulae

You know that you can create a calculated column within a list. This is a very useful feature. Here are some formulae that I've used a few times.

I use these calculated fields for grouping and filtering list items.

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]))


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")