Formula for Auto Reference Number

HI cpould it be possible to indicate time hour and minute in a formulated column i have sample formula in here but its still unparsable i able to create and test with some helper columns but still didnt work

=IF([Case Type]@row = "Internal Service Issue", "ISI",
IF([Case Type]@row = "External Service Issue", "ESI",
IF([Case Type]@row = "Agency Debit Memo", "ADM",
IF([Case Type]@row = "General and Admin", "GEN", "")))) +
TEXT(MONTH(Created@row ), "00") +
TEXT(DAY(Created@row ), "00") +
RIGHT(YEAR(Created@row ), 2) +
TEXT(HOUR(Created@row ), "00") +
TEXT(MINUTE(Created@row ), "00")

Output should be like that

ADM0805250930

can anyone help me with this

Find more posts tagged with

Accepted answers

Paul Newcome

Give this a try:

=LEFT(Created@row, 2) + MID(Created@row, 4, 2) + MID(Created@row, 7, 2) + RIGHT("0" + MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1)) + MID(Created@row, FIND(":", Created@row) + 1, 2)

Paul Newcome

Happy to help. 👍️

All comments

Paul McGuinness

Hi @shaunnelei

You can use this formula to get the result you need, just add this to the back of your if formula instead:

MID(Created@row , 4, 2) + LEFT(Created@row , 2) + MID(Created@row , 7, 2) + MID(Created@row , 10, 2) + RIGHT(Created@row , 2)

Tested and working

Thanks

Paul McGuinness

Smartsheet Overachievers

shaunnelei

amazing paul thankyou for this tried it and the reult on my end has a pm word i just only need the 0805251711 format result

Paul Newcome

Give this a try:

shaunnelei

@Paul Newcome thanks for this ill give it a try will test it

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.