Copy DATE as TEXT and display MONTH only

**onmyway** · 12-06-2013, 04:01 AM

Hi guys!

I am stumped

and would appreciate your help!

I am using the following code in my ThisWorkbook module to copy one field to another when the Workbook is opened:

Please Login or Register  to view this content.

BUT, the field I am copying is a date (Format: [$-F800]dddd, mmmm dd, yyyy), and I would like to copy only the month (Format: mmmm) to display the name of the month, BUT as text only, not as a date format or formula etc. I need to use the month in a pivot.

I would appreciate your help!

**Olly** · 12-06-2013, 04:50 AM

Try:

Please Login or Register  to view this content.

You may also want to specify the worksheet...

**onmyway** · 12-06-2013, 04:57 AM

Hi ollyXLS, thanks for the help!

My problem is, that I not only need the format to be "mmmm" to display the month, I need it to convert the displayed month to text, otherwise the pivot still picks up the original date, as it only displays the month as format, and not text. Any suggestions?

**Olly** · 12-06-2013, 05:00 AM

Originally Posted by onmyway

Hi ollyXLS, thanks for the help!

My problem is, that I not only need the format to be "mmmm" to display the month, I need it to convert the displayed month to text, otherwise the pivot still picks up the original date, as it only displays the month as format, and not text. Any suggestions?

Did you try my code?

**onmyway** · 12-06-2013, 05:01 AM

yes, and it does display the field as mmmm, but in the formula bar it still displays the full date

**Olly** · 12-06-2013, 05:04 AM

Originally Posted by onmyway

yes, and it does display the field as mmmm, but in the formula bar it still displays the full date

Not in AR6, it doesn't.

The FORMAT function takes an expression and returns it as a formatted string.

**onmyway** · 12-06-2013, 05:15 AM

Maybe I am doing something wrong.

I have added your code to mine as below:

Please Login or Register  to view this content.

But it definitely still shows my AR range as "2013-01-31" values.

**onmyway** · 12-06-2013, 05:16 AM

the format shows January in the column, but 2013-01-31 in the formula bar.

**Olly** · 12-06-2013, 05:17 AM

1. Replace all your code with mine, the entire subroutine.

2. Attach your workbook.

**Olly** · 12-06-2013, 05:25 AM

Are you trying to calculate the month (as text) in cell AR6, or in the whole column??! My code above only works with AR6...

If it's for the whole column, try this:

Please Login or Register  to view this content.

**onmyway** · 12-06-2013, 05:31 AM

The whole column, sorry! Could you perhaps change you last code, to start the range from row 9 - this first 8 rows have formulas and headers and stuff.

**Olly** · 12-06-2013, 05:32 AM

Originally Posted by onmyway

The whole column, sorry! Could you perhaps change you last code, to start the range from row 9 - this first 8 rows have formulas and headers and stuff.

You can easily change that - change the 1s to 9s.

**onmyway** · 12-06-2013, 05:55 AM

Legend! Thank you for your patience! You must have been pulling out your hair?!

**Olly** · 12-06-2013, 05:57 AM

Originally Posted by onmyway

Legend! Thank you for your patience! You must have been pulling out your hair?!

Glad we got there

Was my bad, for not taking enough time to understand what your existing code was already doing. I just latched onto the "AR6" reference!

**onmyway** · 12-06-2013, 05:58 AM

cool, thanks!

Copy DATE as TEXT and display MONTH only

LinkBack

Thread Tools

Rate This Thread

Display

Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Re: Copy DATE as TEXT and display MONTH only

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Display the first date of the month when the month and year are selected

[SOLVED] How to display only the month when given date value

Covert Date DD/MM/YYYY in to month, with the month as text

[SOLVED] Date formula with text in same cell but using previous month not current month

Display end of month date for previous month

Bookmarks

Bookmarks

Posting Permissions