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

Beth Fantozzi

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

Devin Lee

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

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

@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

@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!