machine-readable and human-readable text representation of a recurring schedule

Question

We're working on an "import from Excel" feature to speed up bulk data entry, especially when moving data from another B2B system into ours.

Of the 20-ish fields we'll be importing, only one of them is challenging to format in Excel: a recurring schedule. So I'm trying to define a text format that:

• is plain unformatted text that can fit into a single Excel cell (or a small number of cells on one row)
• is machine-readable to avoid import errors
• is human readable to avoid data entry errors and to make it easy to troubleshoot format mistakes if the import fails.

Obviously these requirements are in tension, or this would be an easier solution. ;-)

Our users are non-technical office workers, so "just use XML or JSON" probably won't work here-- too complex for our users, and too verbose too.

Although the app is English-only now, we may eventually have to localize into European languages (although not RTL or East-Asian or other more challenging localizations).

Is what I'm trying to do possible? Or is it such a bad idea that I shouldn't bother?

The alternative is that every time we need to input hundreds of schedules into our app, we'll end up asking our developers write custom import code... and for obvious reasons we really want to avoid this so our customers can self-serve instead.

Today, schedules are very simple: a pattern recurs every 1-4 weeks, and within those 1-4 weeks the same days are selected.

Examples:

• Mon, Wed, Fri (1 week pattern)
• Every 2nd Monday (2 week pattern)
• Every 4th Tuesday (4 week pattern)
• Two-week pattern, with Mon, Wed on first week, and Tue, Thu on second week

Ideally a format could grow over time so if we wanted to support monthly schedules (e.g. 1st and 15th of the month) the format could be extended.

I know that text serialization is often more of a developer-facing topic than a UX topic, but in this case I'm targeting the text format at non-technical users, not developers, so I assumed that UX StackExchange would be appropriate. If you disagree, feel free to vote to migrate to StackOverflow.

Answer 1

Machine-readable content does not necessarily have to have any impact on the visual representation for humans.

Potential solutions for you particular case include hCalendar Microformating and Dates, Times and Durations Microdata

These are both written into the code rather than in the visual representation. This means they also work for RTL, East-Asian and any other language format that you can represent.

Answer 2

This very much is a usability issue, though others might disagree.

Best format I can think of while a little heavy on the columns is the only way to guarantee without requiring users to remember hyper-specialized formating codes:

A              B             C             D             E
FREQUENCY      EVERY 1ST     EVERY 2ND     EVERY 3RD     EVERY 4TH
------------------------------------------------------------------------
Weekly        |Mon, Wed, Fri|
Fortnightly   |              Mon          |
Four Weekly   |                                          Tues          |
Fortnightly   |Mon, Wed      Tue, Thu     |

• Frequency: could allow a very wide range of user input synonyms, or could be strict but using words is more user friendly but otherwise the column could be converted to "Frequency (days)" and hence all input in multiples of 7 [downside to days is that it makes inputing monthly difficult, is a month 28, 30, 30.43, 31 days?]
• As for "Mon,Tue,Wed,Thu..." or "Monday, Tuesday, Wednesday, Thursday...", I would be as flexible as possible for the user and convert it to whatever works best in excel, likely abbreviated but allowing both short and long form is immensely better as the user input.
• Not sure of the connection between user input through App UI and an excel spreadsheet [storing data not a database unless user needs to read/edit it there] but if it is possible to format the spreadsheet [if there is any reason to do so], if the area between the pipe bars was conditionally formatted with light grey BG [against normal white BG] it would make it substantially more obvious to anyone reading down the spreadsheet eg 2 cells highlighted = 2 week cycle, 4 cells highlighted = 4 week cycle without even having to read the frequency column without going overboard which would be to use 28 cells for each day of 4 week cycle.
• Putting all the text into a single column reduces readability severely if there is any reason for users to do so? Putting it into a frequency column and days column is still hap-hazard, and is visually lacking. Though it is hard to work out where the user difficulty is coming from the App side if it is not coming from a mobile version of Office as a dropdown with frequency could be used and then a special calendar could dropdown that would only show 7 days if the user selected a week.