|
Inserting custom date in Excel
Hi,
I want to auto insert custom dates as per this example: Fri 12 01 07 (I then hope it can auto fill a group of cells consecutively ie Sat 13 01 07 Sun 14 01 07 Mon 15 01 07 etc for the week) Can anyone please help me do this. I am most grateful (I have spent time looking at the help files, but cannot find an answer anywhere. Also I am new to Excel & as I am just starting to use it do not fully know or understand it!). Many thanks -- RPD |
Inserting custom date in Excel
Hi
Enter you date in a cell e.g. in cell A1 Right click the cell, FormatCellsNumberCustom ddd dd.mm.yy In the cell beneath enter =A1+1 Hover the mouse over the bottom right corner of cell A2, until it changes to a small black cross (the fill handle) Hold left mouse button down a you drag downward as far as required and it will copy the formula, adjusting as it goes to A2+1, A3+1 etc. to give your range of dates -- Regards Roger Govier "RPD" wrote in message ... Hi, I want to auto insert custom dates as per this example: Fri 12 01 07 (I then hope it can auto fill a group of cells consecutively ie Sat 13 01 07 Sun 14 01 07 Mon 15 01 07 etc for the week) Can anyone please help me do this. I am most grateful (I have spent time looking at the help files, but cannot find an answer anywhere. Also I am new to Excel & as I am just starting to use it do not fully know or understand it!). Many thanks -- RPD |
Inserting custom date in Excel
Hi Roger,
Thanks for your reply & advice. Unfortunately this is not working in the way I wanted eg: I started with: Fri 12 01 06 & after doing this suggestion I now have Fri 12 01 06 Fri 12 01 07 Fri 12 01 08 etc (the year increasing!not the day & without the text changing) What I actually want is: Fri 12 01 06 Sat 13 01 06 Sun 14 01 06 etc Can this be done? I would much appreciate any further help on this.Thank you -- RPD "Roger Govier" wrote: Hi Enter you date in a cell e.g. in cell A1 Right click the cell, FormatCellsNumberCustom ddd dd.mm.yy In the cell beneath enter =A1+1 Hover the mouse over the bottom right corner of cell A2, until it changes to a small black cross (the fill handle) Hold left mouse button down a you drag downward as far as required and it will copy the formula, adjusting as it goes to A2+1, A3+1 etc. to give your range of dates -- Regards Roger Govier "RPD" wrote in message ... Hi, I want to auto insert custom dates as per this example: Fri 12 01 07 (I then hope it can auto fill a group of cells consecutively ie Sat 13 01 07 Sun 14 01 07 Mon 15 01 07 etc for the week) Can anyone please help me do this. I am most grateful (I have spent time looking at the help files, but cannot find an answer anywhere. Also I am new to Excel & as I am just starting to use it do not fully know or understand it!). Many thanks -- RPD |
Inserting custom date in Excel
"RPD" wrote:
.. What I actually want is: Fri 12 01 06 Sat 13 01 06 Sun 14 01 06 Another play to try .. Put this in any starting cell, eg in B2: =TEXT(--"11-Jan-2006"+ROW(A1),"ddd dd mm yy") Copy B2 down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Inserting custom date in Excel
Hi
No I don't think you understood what I said. Enter the start date in the cell as a normal Excel date 12/01/07 Then Format the cell to give the appearance of Fri 12.01.07 by using the custom format of ddd dd.mm.yy In A2 enter =A1 + 1 Use the fill handle at the bottom of cell A2 to copy down It will then show Sat 13.01.07, Sun 14.01.07 etc. You typed Fri 12.01.07 and just dragged down. Doing that, Excel would just increment the last number only -- Regards Roger Govier "RPD" wrote in message ... Hi Roger, Thanks for your reply & advice. Unfortunately this is not working in the way I wanted eg: I started with: Fri 12 01 06 & after doing this suggestion I now have Fri 12 01 06 Fri 12 01 07 Fri 12 01 08 etc (the year increasing!not the day & without the text changing) What I actually want is: Fri 12 01 06 Sat 13 01 06 Sun 14 01 06 etc Can this be done? I would much appreciate any further help on this.Thank you -- RPD "Roger Govier" wrote: Hi Enter you date in a cell e.g. in cell A1 Right click the cell, FormatCellsNumberCustom ddd dd.mm.yy In the cell beneath enter =A1+1 Hover the mouse over the bottom right corner of cell A2, until it changes to a small black cross (the fill handle) Hold left mouse button down a you drag downward as far as required and it will copy the formula, adjusting as it goes to A2+1, A3+1 etc. to give your range of dates -- Regards Roger Govier "RPD" wrote in message ... Hi, I want to auto insert custom dates as per this example: Fri 12 01 07 (I then hope it can auto fill a group of cells consecutively ie Sat 13 01 07 Sun 14 01 07 Mon 15 01 07 etc for the week) Can anyone please help me do this. I am most grateful (I have spent time looking at the help files, but cannot find an answer anywhere. Also I am new to Excel & as I am just starting to use it do not fully know or understand it!). Many thanks -- RPD |
Inserting custom date in Excel
Hi Max & Roger,
Thanks for your help & replies. Max- When I entered your formula by hand I didn't get a result, but when I copied & pasted it in it worked! (I was fairly sure I copied it correctly by hand-I will play around with it some more). Roger- Thanks for your further clarification instruction Initially this is what I reached: Fri 12.01.07 13 January 2007 14 January 2007 After dragging down.Then when I went back & dragged again the format was applied to all cells eg: Fri 12.01.07 Sat 13.01.07 Sun 14.01.07 Which is what I wanted (Hurrah!). After getting this result I hesitate to question it more, but will! When I then tried to edit one of these dates, by clicking in the cell, the text disappeared and the Excel date reappeared after I retyped the text date back in! eg Thu 12/01/2007 I think learning Excel could be a long process for me! Where is the best place to get help with future queries? THANK YOU both VERY much for all your help.Best wishes to you -- RPD "Roger Govier" wrote: Hi No I don't think you understood what I said. Enter the start date in the cell as a normal Excel date 12/01/07 Then Format the cell to give the appearance of Fri 12.01.07 by using the custom format of ddd dd.mm.yy In A2 enter =A1 + 1 Use the fill handle at the bottom of cell A2 to copy down It will then show Sat 13.01.07, Sun 14.01.07 etc. You typed Fri 12.01.07 and just dragged down. Doing that, Excel would just increment the last number only -- Regards Roger Govier "RPD" wrote in message ... Hi Roger, Thanks for your reply & advice. Unfortunately this is not working in the way I wanted eg: I started with: Fri 12 01 06 & after doing this suggestion I now have Fri 12 01 06 Fri 12 01 07 Fri 12 01 08 etc (the year increasing!not the day & without the text changing) What I actually want is: Fri 12 01 06 Sat 13 01 06 Sun 14 01 06 etc Can this be done? I would much appreciate any further help on this.Thank you -- RPD "Roger Govier" wrote: Hi Enter you date in a cell e.g. in cell A1 Right click the cell, FormatCellsNumberCustom ddd dd.mm.yy In the cell beneath enter =A1+1 Hover the mouse over the bottom right corner of cell A2, until it changes to a small black cross (the fill handle) Hold left mouse button down a you drag downward as far as required and it will copy the formula, adjusting as it goes to A2+1, A3+1 etc. to give your range of dates -- Regards Roger Govier "RPD" wrote in message ... Hi, I want to auto insert custom dates as per this example: Fri 12 01 07 (I then hope it can auto fill a group of cells consecutively ie Sat 13 01 07 Sun 14 01 07 Mon 15 01 07 etc for the week) Can anyone please help me do this. I am most grateful (I have spent time looking at the help files, but cannot find an answer anywhere. Also I am new to Excel & as I am just starting to use it do not fully know or understand it!). Many thanks -- RPD |
Inserting custom date in Excel
You're welcome. Just change the year to "07" for the initial date within the
suggested formula to get results consistent with your post, viz. use: In say, B2, copied down: =TEXT(--"11-Jan-2007"+ROW(A1),"ddd dd mm yy") Think I was misled by your example dates in your response to Roger which indicated the year as "06" <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RPD" wrote in message ... Hi Max & Roger, Thanks for your help & replies. Max- When I entered your formula by hand I didn't get a result, but when I copied & pasted it in it worked! (I was fairly sure I copied it correctly by hand-I will play around with it some more). Roger- Thanks for your further clarification instruction Initially this is what I reached: Fri 12.01.07 13 January 2007 14 January 2007 After dragging down.Then when I went back & dragged again the format was applied to all cells eg: Fri 12.01.07 Sat 13.01.07 Sun 14.01.07 Which is what I wanted (Hurrah!). After getting this result I hesitate to question it more, but will! When I then tried to edit one of these dates, by clicking in the cell, the text disappeared and the Excel date reappeared after I retyped the text date back in! eg Thu 12/01/2007 I think learning Excel could be a long process for me! Where is the best place to get help with future queries? THANK YOU both VERY much for all your help.Best wishes to you -- RPD |
Inserting custom date in Excel
Hi
Thanks for the feed back. I'm sure I also speak for Max when I say you are very welcome. Because dates after the first one are formulae, then you can't "edit" them as such as that destroys the formula. Changing the first date in cell A1, would cause all the others to alter automatically. Having had the custom format applied to the cell, just typing 15/01/07 would automatically appear as Mon 15.01.07 and the cells following would have dates following each day after that. As to where do you learn? Debra Dalgleish has a very comprehensive list of books on her site http://www.contextures.com/xlbooks.html#General but maybe John Walkenbach's "Excel for Dummies" would be a good starting point. http://www.amazon.com/exec/obidos/re...reative=373489 Following these newsgroups and reading the questions, setting them up on your machine and trying to understand the answers I always found (and still do) very useful. For future help, just type a question back here. Most folk are pretty helpful, as long as you are willing to try and help yourself as well. -- Regards Roger Govier "RPD" wrote in message ... Hi Max & Roger, Thanks for your help & replies. Max- When I entered your formula by hand I didn't get a result, but when I copied & pasted it in it worked! (I was fairly sure I copied it correctly by hand-I will play around with it some more). Roger- Thanks for your further clarification instruction Initially this is what I reached: Fri 12.01.07 13 January 2007 14 January 2007 After dragging down.Then when I went back & dragged again the format was applied to all cells eg: Fri 12.01.07 Sat 13.01.07 Sun 14.01.07 Which is what I wanted (Hurrah!). After getting this result I hesitate to question it more, but will! When I then tried to edit one of these dates, by clicking in the cell, the text disappeared and the Excel date reappeared after I retyped the text date back in! eg Thu 12/01/2007 I think learning Excel could be a long process for me! Where is the best place to get help with future queries? THANK YOU both VERY much for all your help.Best wishes to you -- RPD "Roger Govier" wrote: Hi No I don't think you understood what I said. Enter the start date in the cell as a normal Excel date 12/01/07 Then Format the cell to give the appearance of Fri 12.01.07 by using the custom format of ddd dd.mm.yy In A2 enter =A1 + 1 Use the fill handle at the bottom of cell A2 to copy down It will then show Sat 13.01.07, Sun 14.01.07 etc. You typed Fri 12.01.07 and just dragged down. Doing that, Excel would just increment the last number only -- Regards Roger Govier "RPD" wrote in message ... Hi Roger, Thanks for your reply & advice. Unfortunately this is not working in the way I wanted eg: I started with: Fri 12 01 06 & after doing this suggestion I now have Fri 12 01 06 Fri 12 01 07 Fri 12 01 08 etc (the year increasing!not the day & without the text changing) What I actually want is: Fri 12 01 06 Sat 13 01 06 Sun 14 01 06 etc Can this be done? I would much appreciate any further help on this.Thank you -- RPD "Roger Govier" wrote: Hi Enter you date in a cell e.g. in cell A1 Right click the cell, FormatCellsNumberCustom ddd dd.mm.yy In the cell beneath enter =A1+1 Hover the mouse over the bottom right corner of cell A2, until it changes to a small black cross (the fill handle) Hold left mouse button down a you drag downward as far as required and it will copy the formula, adjusting as it goes to A2+1, A3+1 etc. to give your range of dates -- Regards Roger Govier "RPD" wrote in message ... Hi, I want to auto insert custom dates as per this example: Fri 12 01 07 (I then hope it can auto fill a group of cells consecutively ie Sat 13 01 07 Sun 14 01 07 Mon 15 01 07 etc for the week) Can anyone please help me do this. I am most grateful (I have spent time looking at the help files, but cannot find an answer anywhere. Also I am new to Excel & as I am just starting to use it do not fully know or understand it!). Many thanks -- RPD |
| All times are GMT +1. The time now is 08:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com