06 Jul Multiple Date Formats #Project
<East London Brewery Jamboree>
If like me, you’re based in the UK and you deal with organisations based in Scandinavia (and others of course) you cannot fail to notice that our Scandinavian colleagues tend to specify dates using the day (Monday = day 1, Tuesday = day 2 etc) of the week and week numbers (W27) and then display them together (e.g. 1/W27) instead of the usual UK method of specifying the date (e.g. 3/7/17). When I’m booking meetings etc using Outlook this isn’t an issue as it has a feature that turns on the week numbers in the calendar which is very useful – thanks Outlook.
However, Project doesn’t have the facility to display both the continental format and the UK format date in a single view, it’s either one or the other and it’s defined globally in the Project Options | General tab, (or specifically for each table).
Recently (and of course coincidentally) I’ve been working with a customer who colloborates with their colleagues in Europe and wants to be able to display both UK format dates (dd/mm/yy) and day/week dates (d/Www) in a single view. Like many problems in MS Project, this can be solved using some custom fields and a formula.
The first thing we need to do is find the function that is responsible for converting dates, and a quick google leads me to the Format function; the table below details the formatting options.
|
Format
|
Description
|
|
d
|
Day of the month in one or two numeric digits, as needed (1 to 31).
|
|
dd
|
Day of the month in two numeric digits (01 to 31).
|
|
ddd
|
First three letters of the weekday (Sun to Sat).
|
|
dddd
|
Full name of the weekday (Sunday to Saturday).
|
|
w
|
Day of the week (1 to 7).
|
|
ww
|
Week of the year (1 to 53).
|
|
m
|
Month of the year in one or two numeric digits, as needed (1 to 12).
|
|
mm
|
Month of the year in two numeric digits (01 to 12).
|
|
mmm
|
First three letters of the month (Jan to Dec).
|
|
mmmm
|
Full name of the month (January to December).
|
|
q
|
The quarter of the year (1 to 4).
|
|
y
|
Number of the day of the year (1 to 366).
|
|
yy
|
Last two digits of the year (01 to 99).
|
|
yyyy
|
Full year (0100 to 9999).
|
So now all I need to do is create a the custom fields that will display the start and finish dates in the d/Www format.
To get the day number in the week of the task’s start date the formula is
format([Start],”w”)
and to get the week number of the task’s start date the formula is
format([Start],”ww”)
and all I need to do is concatenate these together with /W in between the two to get d/Www
format([Start],”w”) & “/W” & format([Start],”ww”)
Please note that if you are working in Europe, the inbuilt d/Www date format utilises the Week starts on: parameter in the Schedule options, and setting this to Monday makes Monday = day 1, Tuesday = day 2 etc.
All I need to do then is set a similar formula for any other dates (baselines, deadlines etc), insert them into the table and I’m done.
Happy days…
Ben.