Skip to main content
Topics#FormulasDomanda di Andrew Church
Andrew Church ha fatto una domanda in #Formulas
28 apr 2016, 17:36

Date of Manufacture Formula Help Please

Good Afternoon All,

I have a date of manufacture formula in excel that pulls the DOM out of a 17 character serial number.  The serial number is a text field as there are 2 letters at the end of it and a letter at the 7th character.

The formula I am using in excel is:

=IF(MID(Cell with the DOM,2,2)>515,SUM(("1/1/201"&MID($Cell with the DOM,8,1)),(MID($Cell with the DOM,9,3)-1)),SUM(("1/1/200"&MID($Cell with the DOM,8,1)),(MID($Cell with the DOM,9,3)-1)))

An example of a serial number is 123456A60560001AB

The characters the formula is looking at are really the 8th-11th (8th is the year, 9-11 is the day of the year)

The formula returns a number like 42425 which when you format the cell comes out to 2/25/2016.

I'm having some trouble translating into Salesforce terms.

Any help is greatly appreciated.

Thanks in advance,

-Andrew

 
7 risposte
  1. George Wright
    30 apr 2016, 21:05
    That error, expecting text, received number, means that the formula return type is text and that the formula is returning a number. What exactly is the excel formula doing? Is there any other parts of the serial number that are important in this calculation? But I did create a formula that returns the date 2/25/2016. This is what I did:

    DATE(2010 + VALUE(MID("123456A60560001AB",8,1)),1,1) + VALUE(MID("123456A60560001AB",9,3)) - 1

    Substitute the sample serial number with the merge field and it will work. I'll break the formula down for you. "DATE(2010 + VALUE(MID("123456A60560001AB",8,1)),1,1)" takes the serial number and looks at the 8th character and uses that number plus 2010 to form the year. MID() returns text so I used VALUE() to turn it into a number. The syntax for the DATE() function looks like this DATE(year, month, day). Therefore it calculates January 1st of the year it was manufactured. "VALUE(MID("123456A60560001AB",9,3))" Takes the day of the year manufactured. In Salesforce, you can add and subtract numbers to dates, so the day of the year is added to January 1st. However, by adding to January 1st, an extra day is produced, so we subtract one day at the end.

    If this helps you or you have more information, please let me know or mark a best answer.

    Thanks,

    Parker
Scrivi una risposta...
0/9000