Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

Text and date formula

I have attached a spreadsheet that should explain what I am trying to do which is:

I want to concatenate text and date, but the date needs to be like 1st  Aug 16, 2nd, 3rd, 4th Aug 16 etc so not like 1/8/16, 1 Aug 16 or 1-8-16

So, Collections JK13 would become Collections BH13 24th Aug 16 after concatenation
dates-and-text.xlsx
0
Jagwarman
Asked:
Jagwarman
  • 4
  • 3
  • 2
2 Solutions
 
ProfessorJimJamCommented:
put this formula in G7

=F7&" "&DAY(G2)&IF(OR(DAY(A2)={1,2,3,21,22,23,31}),
CHOOSE(1*RIGHT(DAY(G2),1),"st","nd ","rd "),"th")&
" "&TEXT(G2,"mmmm, yyyy")
dates-and-text.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Professor
For some dates, date prefix is not working e.g. 21, 22,31 etc. Please revise the formula. :)

Or another formula could be this.....
In G7
=F7&" "&DAY(G2)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(G2))*(ABS(MOD(DAY(G2),100)-12)>1)+1),2)&" "&TEXT(G2,"mmmm, yyyy")

Open in new window

0
 
JagwarmanAuthor Commented:
Superstar Thanks
1
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Jagwarman
I assume that compliment is for both of us. Right? :)
0
 
JagwarmanAuthor Commented:
Subodh Tiwari (Neeraj) they all work for me and so do yours

thanks
0
 
JagwarmanAuthor Commented:
yes brilliant both of you many thanks
0
 
JagwarmanAuthor Commented:
thanks guys
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad we could help.
0
 
ProfessorJimJamCommented:
thanks Jagwarman

thanks Neeraj :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now