Excel custom format, weekday & date line break query….

35 pts.
Tags:
Microsoft Excel
Microsoft Excel 2003
Microsoft Excel formulas
microsoft excel functions
I created a spreadsheet which is to act as a rota, with the day and date displayed to the left. I entered the date then went to the custom format tab and entered dddd dd/mm/yyyy so it would display the day and date. So it can be printed with all the relevant data it is nessacery for this data to be displayed in the wrap text style but with custom formating this dosn't work. I summised I needed to enter a line break and following advice I found online I did the following in custom formating: dddd alt0010 dd/mm/yyyy. This displays the data in the correct fashion but for some reason if I attempt to resize the column width the data is undisplayable?! Have scoured the help topics and internet attempting to find a solution to this but cannot find one. Feel like such a nOOb.... Basically I want the day and date displayed as such: Monday 25/05/2009 In one cell I can make compact, I will be making rotas through out the year so it would be convient to have this function available without the need of ardous manual input. Any suggestions are more then welcome!

Answer Wiki

Thanks. We'll let you know when a new response is added.

i’m not sure i really understood your problem, but try after choosing the date format you want,
in Format Cells also, a tab exists called “alignment”, under “Text Control” enable “Shrink to fit”
this will probably solve your problem

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dangerchef
    But the shrink to fit function does just that, shrinks the text making it too small. This spreadsheet is for printing out and display so correct formating is important. Basically after doing the above, the information is displayed in a single cell with the day above the date, but it also gives the invisble width, basically it looks as though you can adjust the width to make the cell fit the information better but if it goes too small the data is undisplayable but the pointat which it does this is still a disance from the actual visable text. Hopefully I'm explaining this properly, not sure how else to do it as it is seeminly quite a weird issue.
    35 pointsBadges:
    report
  • Findsarfaraz
    I would suggest you to use the following forumla, =TEXT(TODAY(),"ddd") & " " &TEXT(TODAY(),"mm/dd/yyyy") and do let me know if this was helpful. Regards, Sarfaraz Ahmed MS Excel help
    1,395 pointsBadges:
    report
  • Dangerchef
    Hi Thanks for that, couldn't quite get the today function to do what I wanted though slightly altered your formula to the following: =TEXT(DATE(9,5,22),"dddd") & " " & TEXT(DATE(9,5,22),"dd/mm/yyyy") Though this dosn't allow me to automatically fill a colum with sequential dates. Is there a way to make the date function in this formula run sequential, as if I had just entered a date normally? I suspect the TODAY function would work better but I can't seem to assign a value to it other then todays date (is it possible to asign another value?) Apologies if my explanations arn't completley accurate, am attempting to explain things as clearly as possible but i'll admit to being a slight noob....
    35 pointsBadges:
    report
  • SbElectric
    You are on right track – just use Text and “dddd” nothing else. I am assuming you are on Excel 2007. On A2 enter 5/25/2009; on B2 enter 5/26/2009. Put the cursor back on B2 - you will notice a small rectangle on bottom of B2 – just place the mouse & drag it to M2 (or whatever). You should get the dates in sequential order. On A1 use: =Text(A2,”dddd”) to get Monday; or use =Text(A2,”ddd”) to get Mon. Now you may drag the same small rectangle at the bottom A1 to M2 to populate all the days. By the way, my PC is set with US date format. Yours should be set with UK date format. But the operation is the same. Good luck – let us know if it solved your roster printing issue.
    2,540 pointsBadges:
    report
  • SbElectric
    Minor correction - On 2nd time dragging drag from A1 to M1. I am sure you have notice dthe typo!!!
    2,540 pointsBadges:
    report
  • Dangerchef
    Hi, Thanks very much for all your help, that seems to work pretty good. Though makes me wonder why this isn't more easily done in the first place!
    35 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following