Excel custom format, weekday & date line break query….
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!
Software/Hardware used:
Software/Hardware used:
ASKED:
April 7, 2009 4:34 PM
UPDATED:
April 16, 2009 10:33 AM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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
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….
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.
Minor correction – On 2nd time dragging drag from A1 to M1.
I am sure you have notice dthe typo!!!
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!