Thread: How to create an account number based on partials from 2 fields
View Single Post
 
Old 09-29-2014, 09:52 AM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
  
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default
This formula works kind of:

=UPPER(IF(LEFT(D4,4)="THE ",MID(D4,5,3),LEFT(D4,3)))&DAY(D36)&MONTH(D36)

except it only displays one digit if the day or month is less than 10. This can be worked around with a few ifs:

=UPPER(IF(LEFT(D4,4)="THE ",MID(D4,5,3),LEFT(D4,3)))&IF(DAY(D36)<10,"0","")& DAY(D36)&IF(MONTH(D36)<10,"0","")&MONTH(D36)
Reply With Quote