Dates to String but Cell formatted as Number

[Klaster]

Hey there,

I have a weired problem. I'm working on a sheet for our accounting section which has been in use for quite some time, which means, that I'm not supposed to change a hell of a lot in it since it's accounting and everybody is afraid of changes.

Coming to the point, I have the following problem:

(1) I have a date.
(2) I need that date in this format: "mm-yyyy", written in a certain cell.
(3) BUT: the cell should NOT contain the date itself, but JUST the text (some tables in the background need this format)
(4) Changing the cell to text-format is not eligible, since it puts an " ' " in front of the numbers.
(5) The cell should be formatted as a number.

So, in conclusion:
I need a string/number with the date in this format: "mm-yyyy" in the cell formated .NumberFormat = "0".


I know it's weired but as I said, I'm working on an existing workbook...

Any help is greatly appreciated!

Cheers,
Klaster


Edit: I decided to add some code and a screenshot. This code will return the number, which lies behind the date, and write it into the cell. So that's not what I want, but maybe the code helps clearifying what I need anyway.

The screenshot shows the wanted output.

Please Login or Register  to view this content.

screenie.png

[Norie]

What dates do you want in mmm-yyy format?

Do you need code?

You could use the TEXT worksheet function.

Please Login or Register  to view this content.

[Klaster]

What dates do you want in mmm-yyy format?

Do you need code?

You could use the TEXT worksheet function.

Please Login or Register  to view this content.

Hey Norie,
thanks for your reply.

The dates are set as the project's time horizon in another sheet and I add rows to match the time frame (e.g. project: 01-2014 to 05-2014, I need rows with all the months in between). That means that the date is variable. Anyway, I could write the date invisible in some cell.
I tried you function and get the following result (Z1911 is formatted as date):

screenie2.png

After all, I'd prefer a code-based solution, though...

Thanks!

[Norie]

It's still not clear what you want to do.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[Klaster]

Hey Norie,

I created an example workbook and attached it.

A few marks:
Sheet "Team": type in time frame (start and end month)
Sheet "Leistungsliste": Column D is the crucial part here. You can see that the cells are formatted as numbers and show "10-2011".

My code will add a block for every month, when the project horizon is extended. Therefore, I edit the "block" in sheet "Tabelle2". I want the cells in Col D to be exactly like the existing ones in "Leistungsliste". I cast the dates from sheet "Team", calculate how many new blocks I need (<-> how many months need to be added) and copy&paste the block accordingly (hence I edit Col D everytime before I copy&paste it).

I hope it's more understandable now?

playground_example.xlsm

[Norie]

Should I be entering dates in G5 and H5?

[Norie]

Try this - I was missing a y and had too many ms.

Please Login or Register  to view this content.

[Klaster]

Try this - I was missing a y and had too many ms.

Please Login or Register  to view this content.

I tried that before, but the NumberFormat of the cell needs to be "0", because the text-Format automatically puts a " ' " in front of the text. That means, that it looks the same, but it is actually not the same... I need it to be exact because other tables use the information for further calculating.

Isn't it possible to somehow "hard code" the date as a number?

Sth like the following... but this gives me a "40848" etc. Why can't I just get the month and year as hard strings that won't change back to date-format when entered in the cell?!?!

Please Login or Register  to view this content.

[Norie]

Unfortunately when you enter 10-2011 in a cell format as general then Excel sees it as a date so it converts it to one.

Try it yourself by entering 02-2014 in a cell.

How are the further calculations being affected by the '?

If they are perhaps it's best to keep the dates as 'real' dates, use formatting to display them as mm-yyyy and adjust formulas appropriately.

[Klaster]

Unfortunately when you enter 10-2011 in a cell format as general then Excel sees it as a date so it converts it to one.

Try it yourself by entering 02-2014 in a cell.

How are the further calculations being affected by the '?

If they are perhaps it's best to keep the dates as 'real' dates, use formatting to display them as mm-yyyy and adjust formulas appropriately.

Yes, I though the same, but as I said, the workbook already exists in that way. I have no idea how they managed to get the format as it is in sheet "leistungsliste" Col D...

There are some PivotTables in the background, that seem to be unable to deal with the '01-2014 - format. I guess I'll need to make some greater changes to the workbook after all.


Thanks anyway for your help - very much appreciated!

I won't mark the thread solved - who knows if somebody comes up with a magic solution...

[Norie]

I would have thought it would be better for pivot tables if the data included 'real' dates.

That would allow for grouping by month/year and proper chronological sorting.

PS As far as I can see the existing data on the 'Leistungsliste' sheet also has the ' in front of the dates.

[Norie]

How about putting a formula in the column?

Please Login or Register  to view this content.

[Klaster]

How about putting a formula in the column?

Please Login or Register  to view this content.

That gives me the same as shown in the 2nd screenshot, I posted above (see "screenie2"). Another screenshot is attached:

formula.png

and the one from above

[Norie]

Instead of mm-yyyy have you tried tt-jjjj?

[Klaster]

Instead of mm-yyyy have you tried tt-jjjj?

Good call. tt-jjjj works great but obviously shows the day and year. mm-jjjj doesn't seem to work though?!

formula.png

[Norie]

Clutching at straws now.

How about this?

Please Login or Register  to view this content.

[Klaster]

Hey,

sorry for my late reply.
I'll talk with the guy who created the workbook in the first place soonish and hope that he'll shed some light on the issue.

Thanks for your effort! It's really appreciated!

[Klaster]

Just as a little follow-up: turns out, the background mechanisms also work with the '01-2014 -Format. Still a strange issue though.

Thanks again for all the help.