Need to convert weekday to text

[redclaygirl]

I used the format text box in my query design to generate weekdays as text from my date field. It works great. However, I was hoping to produce some graphs where I could use those weekdays as 7 categories to plot against some other data. Now I am finding that to be a problem as the "real" format is a date and when I try to change the format in excel it creates this random number. Any suggestions on how to get around this problem. Thanks a bunch.

 

[mresann]

If this is an Excel chart formatting problem, you may have to set the chart up in VBA with the Excel object model. Can you do this?

 

[redclaygirl]

I will give it a try. Thank you.

 

[redclaygirl]

Actually, I have no idea how to use the VBA in Excel. Any more ideas? It seems like there should be some other way to convert my original date to weekdays so that I can use them as categories. Any help is welcome.

 

[MonsterLucifer]

If I am getting this right, try this:

The Cell that contains the date; Right-Click -> Click 'Format Cells' -> Click 'Number' Tab;

In 'Category' portion, Click 'Custom'; in 'Type:' box, type this without quotes "[$-409]mmmm;@" click Ok.

This will not change the contents of your cell and convert the display to Months and when you'll make chart using this cell, the chart will recognize it as a Month!

Hope it is of help.

NB: Do reply if you find it of any use?

Regards. ML

 

[MonsterLucifer]

Sorry, re-read your question and replying again...

If you're desperate to get this done; try this dirty way

First format your date column; Right-Click Cell; click Format Cells; Click Number tab; Click Custom from Category and in the Type box type without quotes; "dddd".

This will convert your dates display to Weekdays; i.e. Saturday, Sunday, Monday, and so on...

1. Now add another column next to this dates column;
2. Copy the column on which you applied the dddd formatting;
3. Open Notepad;
4. Paste the column in Notepad;
5. Copy everything that you just pasted in Notepad;
6. And paste it in the Excel Column that you created in Step 1;

Now make your chart, I do understand that this is not exactly what you're looking for but it will do the trick (again if I am reading your question from your point of view).

Hope it is of help.

Regards. ML

 

[redclaygirl]

Thank you so much! It works perfectly.