text number convert to month

**leakhna** · 04-19-2019, 07:09 AM

Dear All,

I try many way to convert below text to month "mmm" but it doesn't work.

I use Datevalue, text(cell,"mmm")

Here is the number i want to convert it in to month name "Apr" and then sum the value of each month.
I have attached excel file for more detail:

15.04.2019 22.04.2019 29.04.2019 06.05.2019 13.05.2019 20.05.2019 27.05.2019 03.06.2019 10.06.2019 17.06.2019 24.06.2019 01.07.2019 08.07.2019 15.07.2019

Thanks & looking to hear from your help.

**Norie** · 04-19-2019, 08:04 AM

To get the month try this.

=TEXT(DATEVALUE(SUBSTITUTE(B2,".","/")), "mmm")

For the sum you could try something like this:

=SUMPRODUCT(B3:T3,--(MID($B$2:$T$2,4,2)=TEXT(DATEVALUE("1 "&B$6), "mm")))

**AliGW** · 04-19-2019, 08:05 AM

These values are not dates - they are just text. Enter proper dates into those cells.

**leakhna** · 04-21-2019, 11:19 PM

Hi,

I try datevalue as you mention but it now work its show "#VALUE!"

Can you help to test it again.

Thanks,
Leakhna

**FDibbins** · 04-21-2019, 11:32 PM

If you are using dates, tt is almost ALWAYS better to stick with the same format thoughout - and always easier if you stay with REAL dates.
15.04.2019 is not a date, it is text that just looks like a date.
Apr is also not a date (to excel), it is just 3 random letters

If you really must used mixed dates like this, the above SHOULD work, but he is a hack that does what you want, without the need to change anything.

B7=SUMIF($B$2:$T$2,"*."&TEXT(MATCH(B6,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),"0#")&".*",$B3:$T3)
copied down and across as needed

text number convert to month

LinkBack

Thread Tools

Rate This Thread

Display