Skip to main content



3 digit date index



  • Avatar
    Peter Thane

    Where does the U come from for 31st Jan? 

    Is it the Julian Date you are trying to encode so 31st Jan is actually 031 and the 00U is a typo? If so if you make the date substring a Clock/Date Field and use the custom format you can just type the format as jjj in the box and will return the Julian date.

  • Avatar
    Gerardo P

    not a typo, more like a base 36 number (hexa=16 0-9a-f   this one=36  0-9a-z)

    0123456789 abcdefghij     klmnopqrst    uvwxyz

    0123456789 0123456789 0123456789 0


  • Avatar
    Gerardo P

    I guess I will have to wait another month to solve the urgent matter lol , is this kind of support available via support tickets instead of forum?

  • Avatar
    Gene Henson

    I don't know if I fully understand the logic. 1/1/2017 is the 31st day of the year, so it's easy to understand why that's 00U. Following that logic, 2/6/2017 would be something like 010? Would today (6/6/2018) follow the same logic?

    If that's correct, and 000 is always 1/1/2017, then you should be able to write a VB script that calculates the number of days from 1/1/2017 to now (or whatever the date value is). You'd then have to write a custom algorithm to convert that number to your base 36 number.

    This kind of custom work wouldn't typically be handle by technical support, although you could contact us to inquire about professional services.


  • Avatar
    Gerardo P

    Yes you got it, (1/1/2017 is the 1rst not the 31st but i think you got mixed up but actually understood),

    6/6/2018 would be 0EH,

    I was actually trying to find if this was a standard ISO/convention for date format for specific purpose or something similar which apparently is not, but more like our client own way of storing dates.

    I think I got it working using different approach, using serialization and custom sequence

    with a date datasource of DATEDIFF("d","01/01/2017",now())   containing only the days since 1/1/2017,

    increment when data datasource changes (once each day),



  • Avatar
    Peter Thane

    Hi Gerado,

    That makes more sense now you said it was base 36. 

    I was going to suggest that may need to use a DIM statement as part of this. Convert the date you want to the Julian value, divide by the base giving a whole number for the second digit and then multiply back up the remainder/Mod and then extract that value from the DIM statement, if that makes sense.

    After a quick Google I came across this which may be of help with some of the code although I haven't tried it myself.




        ' Convert Base10 to any other Base (2 to 36)
        ' From forum C# code - converted to VB

        Public Function convertToBase(ByVal value As Int32, base As Int32) As String

            Dim chars As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
            Dim r As Int32
            Dim newNumber As String

            If (base < 2 Or base > chars.Length) Then
                Return ""
            End If

            newNumber = ""

            While value >= base
                r = value Mod base
                newNumber = chars.Substring(r, 1) & newNumber
                value = Convert.ItInt32(Math.Floor(value / base))     ' Don't want to round up
            End While
            If value > 0 Then
                newNumber = chars.Substring(value, 1) & newNumber
            End If

            If String.IsNullOrEmpty(newNumber) Then
                newNumber = "0"
            End If

            r = Nothing
            chars = Nothing

            Return newNumber

        End Function


  • Avatar
    Gene Henson

    It seems like that custom serialization should work for your case. If you wanted to do this via VB Script, or need to calculate it when the endDate doesn't equal today, I think this script will help. It hasn't been rigorously tested, but I did run it through a couple of the dates talked about in this thread.

    Dim endate, startDate, daysElapsed, symbols, result

    symbols = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    startDate = "1/1/2017" ' Date in d/m/yyyy

    ' Get Number of days between startDate and endDate
    daysElapsed = DateDiff("d", startDate, endDate)

    ' Handle the case of startDate matching endDate
    If daysElapsed = 0 Then
    result = "000"
    ' Recursively converts to base 36
    Do While daysElapsed > 0
    result = Mid(symbols,(daysElapsed Mod 36) +1, 1) & result
    daysElapsed = Int(daysElapsed/36)
    End If

    Value = result

Please sign in to leave a comment.