Monday to Mon?

[billsharp]

Hi All. New to forum, so please be gentle.

I am using a microsoft form which creates an excel sheet that contains days of the week as Monday, Tuesday, etc. I would like to format the column containing these days of the week to 3 characters, Mon, Tue, etc. I have tried the format cells but can't see a way to do it. I can add it to a macro if necessary. Thanks for your help.

 

[jasonb75]

How are the weekdays entered into the cells?

Are they dates formatted as dddd? Text strings typed in? Created by formulas, or something else?

Dates formatted as dddd simply need to be changed to ddd.

Other possibilities will have many different answers.

edit:- generic signature below this line, not part of this message

 

[billsharp]

How are the weekdays entered into the cells?

Are they dates formatted as dddd? Text strings typed in? Created by formulas, or something else?

Dates formatted as dddd simply need to be changed to ddd.

Other possibilities will have many different answers.

edit:- generic signature below this line, not part of this message

The data is entered as the day of the week. I.e Monday, Tuesday, Wednesday, etc. The day of the week is in text format and is not taken from a date. I have a macro that sorts out the spreadsheet created by MSforms to adjust column widths, add headers, etc. I need to reduce the width of the day of the week column to allow more date in the other columns, hence the reason to reduce the day of the week to 3 characters.

Thanks for your help.

 

[Rick Rothstein]

If the Monday, Tuesday, etc. are text, you cannot format the cells they are in to display as abbreviations. You can do what you want using VBA event code, but we would need to know what range of cells those day names are being placed into. I wonder though, if the values are being entered by users, why don't they just type the first three letters and stop... why are they typing in the full names when it is not needed?

 

[jasonb75]

Assuming that the weekdays are being imported in that format rather than generated / typed individually, I would suggest using text to columns to split them down.

This should do the trick, where the variable rng refers to the range containing the weekdays (not including column header).

rng.TextToColumns Destination:=rng(1), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 9)), TrailingMinusNumbers:=True

 

[billsharp]

If the Monday, Tuesday, etc. are text, you cannot format the cells they are in to display as abbreviations. You can do what you want using VBA event code, but we would need to know what range of cells those day names are being placed into. I wonder though, if the values are being entered by users, why don't they just type the first three letters and stop... why are they typing in the full names when it is not needed?

The users are selecting the day of the week from a list in a MSform. The easiest way would be for me to change the options in the form. I just though this would be a simple thing to do in a macro or through cell formatting, but I see it is more complicated than I thought.

Thanks for everyones help.

 

[Rick Rothstein]

It is simple to do in a macro if that is what you want. I suggested event code to make it dynamic like formatting would be... user puts a value in a monitored cell and it would instant change to the abbreviation. Again, to do that, we would need to know the cell range where the day names could be placed. A macro, on the other hand, is run whenever you want and is not dynamic. It can be keyed off the selected cell (in which case the code would not need to know the range where day names could be placed) or a specified range where day names are placed. I (or another volunteer) would be happy to give you a VBA coded solution, just tell us whether you want event code or a macro and tell us where the day names can be placed.