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]

Share this blog post:

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Kris Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
newest oldest most voted
Notify of
Kris
Guest
Kris

When I try this,

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

If it is February, it only show 2 instead of 02. This is not helpful to me as I need to update another date column, but it is not showing correctly. If November is selected, the month shows correctly at 11? Any ideas?