 Track this topic | Kullanıcı | Konuyu gönderen: David Jessop Excel 2007: Add a date for all days of the week after Monday |
|---|
David Jessop 
İspanya
  Üye
İspanyolca > İngilizce
+ ...
|
Hi...
I don´t have much Excel experience and so I am thinking this is a fairly easy formula question for anyone who does. Thanks for any help in advance!
I have a client for whom I would like to do time tracking on a weekly basis and I will be working every day with him. So I am creating a spreadsheet with the days of the week, beginning with Monday. Say the date on Monday of this week is 07/06/09. I want to have the spreadsheet automatically populate the remaining 6 days of the week with dates, so I don´t have to do it manually.
So say the list is:
Mon
Tues
Wed
Thurs
Fri
Sat
Sun
I will manually enter 07/06/09 in B1 (next to "Monday", assuming that Monday is in A1). Then the following would happen automatically:
Tues 07/07/09
Wed 07/08/09
Thurs 07/09/09
Fri 07/10/09
Sat 07/11/09
Sun 07/12/09
Get my drift?
Best,
David
| | | | Katalin Horvath McClure
Amerika Birleşik Devletleri
Local time: 03:29
2002 tarihinden beri üye
İngilizce > Macarca
+ ...
| | Not fully automatic, but here is one way | Jul 3 |
David,
They key is to turn the date you type in into the day of the week (as a text).
If you type in the date to cell A1, and you would have a formula in B1 to produce the name of the day, then all you have to do is to drag these two cells down in your spreadsheet as far as you want, and the date would be incremented automatically, and the formula would work in the B cells.
There are two ways of doing this (let's assume you have the first date typed intocell A1):
Method 1. Format cells to show dates as the day of the week
Select the cells that contain dates that you want to show as the days of the week.
On the Home tab, in the Number group, click the arrow, click More Number Formats, and then click the Number tab.
Under Category, click Custom, and in the Type box, type dddd for the full name of the day of the week (Monday, Tuesday, and so on), or ddd for the abbreviated name of the day of the week (Mon, Tue, Wed, and so on).
(This works in Excel 2007, I can't check the exact location of this setting in earlier versions.)
Method 2. Convert dates to the text for the day of the week
To do this task, use the TEXT function in cell B1, like this:.
=TEXT(A1, "dddd") Calculates the day of the week for the date and returns the full name of the day of the week (for example Thursday)
=TEXT(A1, "ddd") Calculates the day of the week for the date and returns the abbreviated name of the day of the week (for example Thu)
This is more likely to work in previous versions as well.
Once you have your B1 cell set up correctly, you just need to drag A1 and B1 downwards.
Hope this helps
Katalin
[Edited at 2009-07-03 23:36 GMT]
  | | | | Tony M
Fransa
Local time: 09:29
Üye
Fransızca > İngilizce
+ ...
MODERATÖR |
Enter the start date in B1
In B2, enter the formula =B1+1 (but make sure B2 is formated to display as a date!)
Fill down column B
I can't for sure say that this will work in '2007, since I still use '2003 — but it is a general principle which ought to work in almost any spreadsheet.
| | | | mariale125
Peru
Local time: 03:29
İngilizce > İspanyolca
|
Hi David, It's been almost a month and you may already have your calendar finished. However this is what I’d have done. I use Office 2003 and it is in Spanish so names may vary.
Write the number 1 in a cell. Then go to Format > Cells. Choose Number, in the Category box choose Date and in the Type box choose a format. However as I my Excel is in Spanish I had to check English in the Regional Configuration box (Configuración regional) in order to get formats in English. Then click OK and voila! the number 1 has been transformed in: January 1, 1900. Write 400025 and it will appear today's date: July 31, 2009. You choose the format you prefer.
You can also edit those formats and create new customized ones. Go to Format > Cells. Choose Number, in the Category box choose the last option Custom. In Type there are two boxes. In the second box look for the date formats and choose one with a similar format to the one you need. You can erase or add elements, just take into account that the new format you create has to respect the coding.
Well, back to the spreadsheet, place the cursor in the right bottom of the cell and when it turns into a black cross drag it down and you will have your calendar without having to type each day one by one.
Hope you find this useful.
Have a nice weekend
Bye
[Editado a las 2009-07-31 23:09 GMT]
| | | | To report site rules violations or get help, contact a site moderator | Excel 2007: Add a date for all days of the week after Monday |
