Search code examples
lotus-noteslotus-domino

How to create double-digit months for "yyyy-mm" string in View?

I'm making a categorized column for year-month for my view and have this as a formula:

@Text(@Year (CertEnd:CQIEnd:InspEnd))  + "-" + @Text(@Month(CertEnd:CQIEnd:InspEnd))

If the month any anything before October, the month comes out as single digit.

How to make single digit months double digit, because I want to add sorting as well?

Solution

  • Using dates as a category sometimes gets mixed up. In my experience, it is best to convert the date to a string if you want to use it as a category. To get the formatting right, use the following formula:

    cert := @text(@year(certEnd)) + "-" + @right("00" + @text(@month(certEnd));2);
cqi := @text(@year(CQIEnd)) + "-" + @right("00" + @text(@month(CQIEnd));2);
insp := @text(@year(InspEnd)) + "-" + @right("00" + @text(@month(InspEnd));2);
cert:cqi:insp

    As an alternative; you can convert cert; cqi and insp like this:

    cert := @text(@year(CertEnd)*100 + @month(CertEnd));
cert := @left(cert;4) + "-" + @right(cert;2)

    This is more readable; but this takes two rows per field.