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)

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.

The action rule is as follows:

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:

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

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

You need to give me some idea of what you did and what your formula contained

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.

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?

Nevermind. I re-copied both formulas and replaces the dashes. It is working now. Your insructions are for 0-7, but it is actually 0-6 after testing this. Thank you for this!

Thanks Jon for both your fixes. 0-7 clearly an 8 day weak…. Duh

I am getting 30th Jan 2016 as Friday