Month always in 2 digits instead of 1/2 digits

[gbrouwer]

Hi,

I try to create a textfield with the month in 2 digits, but I can't see what I should change in the formula. 

Required output (yy"M"mm):

18M01

18M02

18M03

..

18M10

18M11

18M12

I tried the following formula:

DateTimeFormat([Closing_Date],'%yM') + ToString(Ceil(ToNumber(DateTimeFormat([Closing_Date],'%m'))))

or this formula:

DateTimeFormat([Closing_Date],'%yM') + ToString(Ceil(ToNumber(DateTimeFormat([Closing_Date],'%mm'))))

Closing_Date is a regular datetime field.

Only with the above formula the month is now 1 (before October) or 2 digits.

18M1

18M2

18M3

..

18M10

18M11

18M12

Who can help me with the right formula?

Kind regards,

Geert

[NicoleJohnson · Accepted solution]

You should actually be able to combine them into the same DateTime formula! Try this: DateTimeFormat([Closing_Date],"%yM%m")

Only those items preceded by the % will pull in the DateTime values, but the M in the middle, since it doesn't have a % in front, will just show up as the letter M. I think the ToString(Ceil part of your formula is what is resulting in the 1 digit month... if you don't include the ToString part, the formula DateTimeFormat([Closing_Date],"%m") in the second half of your original formula would also work.

Cheers,

NJ

[jamielaird · Accepted solution]

Hi @gbrouwer,

This should do the trick:

DateTimeFormat([Closing_Date],'%yM')+
IF DateTimeMonth([Closing_Date])<10 THEN '0'+ToString(DateTimeMonth([Closing_Date]))
ELSE ToString(DateTimeMonth([Closing_Date]))
ENDIF

Quick example attached.

[gbrouwer]

Thanks.

Both solutions works.

Kind regards,

Geert

[gbrouwer]

Thanks.

Both solutions works.

When I see the solution... it was much easier then I was thinking.

Kind regards,

Geert