I need to create a "Service Report #" that pulls info from 3 cells

Options

Hi there!

I need to create a "Service Report #" that pulls info from 3 cells:

--"Portfolio"

--"Site"

--"Date"

Where for Portfolio and Site are an abbreviation of what's in the cell (i.e, "Newport News" becomes "NN"/"Middlesex" becomes "MSX" and the date is 6 numbers with no punctuation (i.e, 01/24/23 becomes 012423). My first inclination is to create (and then hide) columns that convert Newport News to NN and the date to a string of numbers. Am I thinking right? Or is there a better way?

And the final result (the Service Report #) is in this format: Portfolio_Site_Date

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Options

    You can convert the date to a 6 digit number using the following formula.

    =RIGHT(MONTH(Date@row) + 100, 2) + RIGHT(DAY(Date@row) + 100, 2) + RIGHT(YEAR(Date@row) + 100, 2)
    

    As for the Portfolio and Site you can write up a formula to convert it but in my experience you are better off making another sheet that has two columns Abbreviation and Full where you input all the options and then using an index/match to lookup the value. That way you are not writing a super long formula and if you need to add new ones it's as simple as adding it to that sheet. In the Full column you will want to use your Portfolio_Site formating.

    =index({Abb Sheet - Full},match([Portfolio and Site]@row,{Abb Sheet - Abbreviation},0),0)
    

    If you are feeling fancy you can combine the two formulas with a +"_"+ in-between them to avoid having hidden columns.

    =index({Abb Sheet - Full},match([Portfolio and Site]@row,{Abb Sheet - Abbreviation},0),0)+"_"+RIGHT(MONTH(Date@row) + 100, 2) + RIGHT(DAY(Date@row) + 100, 2) + RIGHT(YEAR(Date@row) + 100, 2)
    


  • Julie Fortney
    Julie Fortney Overachievers
    Options

    Hi @Beth Fantozzi

    Here's how I would set it up:

    And here are the formulas:

    Service Report #

    =[Portfolio Abbreviation]@row + "_" + [Site Abbreviation]@row + "_" + [Date String]@row

    Portfolio Abbreviation

    You could create an IF formula such as =IF(Portfolio@row = "Newport News", "NN"... but if you have more than a few portfolios, I would create a master list with two columns, "Portfolio" and "Portfolio Abbreviation," then use an Index Match formula to pull the correct abbreviation into the column on this main sheet.

    If you want to go this route and need help with the Index Match formula, just let me know.

    Site Abbreviation

    Same suggestion as Portfolio Abbreviation

    Month

    =RIGHT(100 + MONTH(Date@row), 2)

    Day

    =RIGHT(100 + DAY(Date@row), 2)

    Year

    =RIGHT(YEAR(Date@row), 2)

    Date String

    =JOIN(Month@row:Year@row)


    www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

  • Beth Fantozzi
    Options

    @Julie Fortney Thank you so much for taking the time to help here. I am still stuck! For the life of me, I cannot get the month and the day to return a 2-digit answer. For the year, I was able to do =RIGHT([Date of Service Visit]@row, 2) and got the last two digits of the string, making it the 2 digit year. I must be doing something wrong that's also simple but I'm not seeing it. Here's the formulas copy/pasted from my sheet:

    =RIGHT(100 + DAY([Date of Service Visit]@row, 2))

    =RIGHT(100 + MONTH([Date of Service Visit]@row, 2))

  • Beth Fantozzi
    Options

    @Julie Fortney

    I may have just answered my question by using the "MID" formula as follows:

    =MID([Date of Service Visit]@row, 4, 2)

    That gave me the middle two numbers for of the day. Now I'll try the "LEFT" Formula for the Month.

    I think I might have this!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!