InfoPath and Day of Week function

The first InfoPath project that I carried out was for a pub chain.  Representatives would visit the bars and fill in a paper survey of atmosphere and service.  These paper surveys were then keyed into Excel and manually summarised onto several other spreadsheets.   I developed an InfoPath form that could be used instead and then published to a SharePoint Form library.  Views on the library would allow forms to be summarised by venue, area, month etc.

On the paper form they recorded the date and the day of the week so they could use that as a dimension to report on.  I thought that if I’m producing an interactive form then it would be daft to choose a date from a date picker and then have to tell the computer what day of the week that relates to.  Of course that’s really easy in Excel, or almost any other application; it turns out the same isn’t true with InfoPath.  This was InfoPath 2003 with SharePoint Portal Server 2003 but the situation hasn’t improved much since then.

InfoPath with its limited set of functions doesn’t have a day of week function at all.  Meaning that you would need to use script, or now managed code, to convert dates into a day.  This set me off looking for a way to use an InfoPath formula instead.  I came across a thing called Zeller’s Congruence; Merlyn and Wikipedia talk about it as shown below:
http://www.merlyn.demon.co.uk/zeller-c.htm
http://en.wikipedia.org/wiki/Zeller’s_congruence

Well the Zeller formula is pretty complex, uses the MOD function that is also missing from InfoPath and needs different handling for Jan and Feb vs the rest of the year.  I’ve taken just that component and put it into a form of its own (shown below)

formdesign

I have a single date field and a text field for day of the week.  I converted the text field into a dropdown list using numbers (0-6) as values and days (Sunday – Saturday) as labels.  There are then two action rules on date box.  The first action rule has a condition to detect January or February dates and execution of further rules does not continue when this one fires.

condition1

The action rule is as follows:

rule1 formula1

The formula is below:

Jan and Feb Zel
round((((floor((26 * number(substring(., 6, 2) + 10) – 2) / 10) + floor(number(substring(., 9, 2))) + floor((number(substring(., 3, 2)) – 1) * 1.25) + floor(number(substring(., 1, 2)) / 4) – floor(number(substring(., 1, 2)) * 2)) / 7) – floor((floor((26 * number(substring(., 6, 2) + 10) – 2) / 10) + floor(number(substring(., 9, 2))) + floor((number(substring(., 3, 2)) – 1) * 1.25) + floor(number(substring(., 1, 2)) / 4) – floor(number(substring(., 1, 2)) * 2)) / 7)) * 7)

The rest of the year rule has the following action:

rule2 formula2

Rest of Year Zel
round((((floor((26 * number(substring(., 6, 2) – 2) – 2) / 10) + floor(number(substring(., 9, 2))) + floor((number(substring(., 3, 2))) * 1.25) + floor(number(substring(., 1, 2)) / 4) – floor(number(substring(., 1, 2)) * 2)) / 7) – floor((floor((26 * number(substring(., 6, 2) – 2) – 2) / 10) + floor(number(substring(., 9, 2))) + floor((number(substring(., 3, 2))) * 1.25) + floor(number(substring(., 1, 2)) / 4) – floor(number(substring(., 1, 2)) * 2)) / 7)) * 7)

The end result of this is that when you set a date, the dropdown box will show you the weekday for that date.  This is achieved without the need for code or script but using just InfoPath formula and rules

Advertisements
This entry was posted in InfoPath and tagged , , , . Bookmark the permalink.

7 Responses to InfoPath and Day of Week function

  1. Michael Campbell says:

    I tried this but I got an “The formula contains one or more errors” – “Unexpected Character”

  2. Jon says:

    PROBLEM ABOVE IS SOLVED. Copy and paste his formulas as instructed in this guide. Where ever you find a ‘minus’ sign, backspace it and re-type in a new minus sign. The ASCI table must have two different types…one meaning dash and one meaning minus. Changing the dashes to minus signs corrects the formula.

  3. Jon says:

    New problem… the December 1, 2012 is coming up as a Sunday. I think the formula is looking for the 31st day in that month…

    Thoughts?

  4. Prasenjeet says:

    I am getting 30th Jan 2016 as Friday

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s