1

I had two calculated columns "Month" and "Year" that displayed forms grouped by these columns. They were calculated from the last modified column. Using =TEXT(Modified,"yyyy") and =TEXT(Modified,"mmmm") which worked fine until I relinked everything it now the modified dates are all the same.

Every time a form is submitted it has the date at the end of the "Title" string. They will never have the exact same character length but the date is always at the end. Here are some examples:

  • Daily Report_ Aaron Stinson - Weatherford - 2015-03-11
  • Daily Report_ Andrew Beck - Minot - 2015-02-03

Is there a way to use a formula to pull the month and year out of the "Title" string?

I've been playing with LEFT, RIGHT, and LEN trying to figure this out but having no luck yet.

1

You can use below formula

=RIGHT(Title, 2) 

=LEFT(RIGHT(Title,10), 4)

First formula will give 03 from Daily Report_ Andrew Beck - Minot - 2015-02-03

Second formula will give 2015 from Daily Report_ Andrew Beck - Minot - 2015-02-03

If you want day also then use below formula

=LEFT(RIGHT(Title,5), 2)
  • Thank you, it set me on the right track to get what I wanted. – Aaron Apr 17 '15 at 17:08
0

For some reason it wants to also pull the .xml file extension at the end. So I just had to adjust the character positions.

This is what I used for the month

=TEXT(LEFT(RIGHT(Title,14),10),"mmmm")

This is what I used for the year.

=TEXT(LEFT(RIGHT(Title,14),10),"yyyy")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.