0

I'm struggling with a forumla, I know it has been answered a million times before but I cannot seem to get it to work.

I have an opportunity name in the format of 0003000000 - 01/12/16 - Other Text

I am wanting to extract the date from that opportunity name and populate a date field.

The current formula I am using is:

datevalue('20'+ mid(name 20,2) + '-' + mid(name 18,2) + '-' + mid(name 14,2))

I would have thought this would have given me 2016-12-01 using the example above. However, when I try to save the opportunity with the field update active it throws an invalid date error.

Would anyone be able to correct my forumla so that it will work properly?

Thanks for your help

Ta Joe

Improve this question
3
  • I think you may have gotten the character counts wrong? Shouldn't there be a 3 character difference between the mid's? So that would make 20, 17 and 14?
    – Kasper
    Commented Oct 28, 2016 at 9:07
  • Absolute legend! That's done it. It was so simple, Thanks for your help :)
    – Joe Hayes
    Commented Oct 28, 2016 at 9:15
  • No problem :), we've all had this happen. Let me put it in an answer and than we can mark the question solved.
    – Kasper
    Commented Oct 28, 2016 at 9:19

1 Answer 1

Reset to default
0

I think you may have gotten the character counts wrong? There should probably be a 3 character difference between the mid's. So that would make:

datevalue('20'+ mid(name 20,2) + '-' + mid(name 17,2) + '-' + mid(name 14,2))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .