Solved

Text and date formula

Posted on 2016-08-24
9
53 Views
Last Modified: 2016-08-24
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
Comment
Question by:Jagwarman
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 250 total points
ID: 41768490
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
 
LVL 29

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41768558
@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
 

Author Comment

by:Jagwarman
ID: 41768560
Superstar Thanks
1
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41768564
@Jagwarman
I assume that compliment is for both of us. Right? :)
0
 

Author Comment

by:Jagwarman
ID: 41768589
Subodh Tiwari (Neeraj) they all work for me and so do yours

thanks
0
 

Author Comment

by:Jagwarman
ID: 41768613
yes brilliant both of you many thanks
0
 

Author Closing Comment

by:Jagwarman
ID: 41768614
thanks guys
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41768633
You're welcome. Glad we could help.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41768648
thanks Jagwarman

thanks Neeraj :)
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question