Tuesday, September 18, 2012

Working with Date formats in InfoPath

To extract the day number of a date

number(substring(DateField, 9, 2))


To extract the month number of a date
number(substring(DateField, 6, 2))


To extract the year number of a date
number(substring(DateField, 1, 4))


Calculate hours

addSeconds(now(), 7200)


Get the month name from a date (and time) field

=TEXT([DateField];"mmmm")


Get three characters of the month name
=TEXT([DateField];"mmm")


Sort or group a view by month name

To sort, add the month number in front
=TEXT(month([DateField]); "00")&" "&TEXT([DateField];"mmmm")

Add two calculated columns that will take the start and the end of the month.

"Start of Month"  =DATE(YEAR([DateField]), MONTH([DateField]), 1)

"End of Month"    =DATE(YEAR([DateField]), MONTH([DateField])+1,1)-1

Add a filter in the view:
"Start of Month" is less than or equal to [Today]
 AND
"End of Month" is greater than or equal to [Today]

No comments: