convert single or double digit number to 2 digit month
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have months listed in a column as single digits representing the month of the year as shown here below. To be consistent with other files I am joining this data to, I need to combine these with a year column and add a leading zero only when it is a single digit month as sown in the update (i.e. "07" from "7" for July). Can anyone help me to do this? I've tried several methods unsuccessfully to even produce the month numbers with a leading zero, without the leading zero also on the 10-12th months (010,011,012) let alone combining the year and month fields as shown her as "Period". Any help would be greatly appreciated.
Current | Update | ||
Year | month | month | Period |
2019 | 1 | 01 | 2019-01 |
2019 | 2 | 02 | 2019-02 |
2019 | 3 | 03 | 2019-03 |
2019 | 4 | 04 | 2019-04 |
2019 | 5 | 05 | 2019-05 |
2019 | 6 | 06 | 2019-06 |
2019 | 7 | 07 | 2019-07 |
2019 | 8 | 08 | 2019-08 |
2019 | 9 | 09 | 2019-09 |
2019 | 10 | 10 | 2019-10 |
2019 | 11 | 11 | 2019-11 |
2019 | 12 | 12 | 2019-12 |
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
padleft(tostring([month]),2,"0") should add the leading 0 when necessary. then [Year]+"-" + [Month] would get your period field. If the data types aren't correct, you can use tostring and tonumber to convert between strings and numbers..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Patrick,
I was finally able to come up with a solution that worked before I saw your response, but yours is definitely much cleaner and efficient. Here is what I did that worked.
IF [month] = "10" THEN [year] + "-" = [month]
ELSEIF [month] = "11" THEN [year] + "-" = [month]
ELSEIF [month] = "12" THEN [year] + "-" = [month]
ELSE [year] + "-0" + [month]
ENDIF
I tested your solution and combined it into a single formula and it worked perfectly for me. I replaced my formula with yours. Thank you!
[Year]+ "-" + padleft(tostring([month]),2,"0")
