Certain value according to a string text

[ganzzu]

Hi,

In a database I'm using, column A is the Date. Based on the value the cells from that column I built a formula so that I get the month name: =IF(AND(A3>=41275,A3<=41305),"January",IF(AND(A3>=41306,A3<=41333),"February" etc.

The format of the cells in column A has to be changed and instead of 8/5/2013 (Date format) I now have 01.17.2013 (text format). I now need to change my formula accordingly so I still get the month name.

I thought of using something like this:=IF(LEFT(A4,2)="01","January"), etc .. but I can't get the formula working for all the months (I think I need several IFs there).

Can you please help me with that? Or maybe you have a different proposal on how this can be solved?

Thanks!

 

[NARAYANK991]

Hi ,

A couple of points :

1. If you have a valid date in say A1 , then to get the month of that date , in B1 , enter the following formula in B1 : =TEXT(A1,"mmmm")

2. What is your native date format ? Is it mm/dd/yyyy or dd/mm/yyyy or anything else ? If it is mm/dd/yyyy , and your data is in text format mm.dd.yyyy , then use the following formula :

=TEXT(DATEVALUE(SUBSTITUTE(A1,".","/")),"mmmm")

Narayan

 

[ganzzu]

Hi Narayan and thank you for the hint.

I will use it when dealing with the database myself so that I won't change the Month name formula.

But there are also other people who will keep using the other date format (mm.dd.yyyy) so it's easier to implement for them the next formula:
=IF(LEFT(A19,2)="01","JAN",IF(LEFT(A19,2)="02","FEB",IF(LEFT(A19,2)="03","MAR" etc.

On the other hand, maybe I can combine the 2 formulas into a bigger one (add both variables) that covers both ways of displaying the date.

I will try to build this and let you know how it goes.

Thanks!

 

[NARAYANK991]

Hi ,

As an alternative to all the IFs and repetitions of the LEFT function , you can use :

=CHOOSE(LEFT(P6,2)+0,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

The +0 converts the string obtained from LEFT(P6,2) to a number.

Narayan

 

[ganzzu]

Hi Narayan,

The above function works well and I will use it for the database.

Thanks again!

 

[Haseeb A]

Hello,

Another way:

=TEXT(LEFT(P6,2)*28,"mmmm")

 

[SirJB7]

Hi, ganzzu!
If you need the obtained value to be a string, I'd follow Haseeb A's suggestion.
But if it's just a displayed string that in fact might be the actual date of column A, you'd simply do this:
Z1: =A1
and format column Z as Custom with format mmmm or mmm for the short way.
Regards!