Excel 2007: Add a date for all days of the week after Monday
Thread poster: David Jessop

David Jessop  Identity Verified
Spain
Member
Spanish to English
+ ...
Jul 3, 2009

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 Horváth McClure  Identity Verified
United States
Local time: 03:24
Member (2002)
English to Hungarian
+ ...
Not fully automatic, but here is one way Jul 3, 2009

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
France
Local time: 09:24
Member
French to English
+ ...
Alternatively... Jul 4, 2009

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
Local time: 02:24
English to Spanish
Try this ... Jul 31, 2009

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:


You can also contact site staff by submitting a support request »

Excel 2007: Add a date for all days of the week after Monday

Advanced search






SDL MultiTerm 2019
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2019 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2019 you can automatically create term lists from your existing documentation to save time.

More info »
SDL Trados Studio 2019 Freelance
The leading translation software used by over 250,000 translators.

SDL Trados Studio 2019 has evolved to bring translators a brand new experience. Designed with user experience at its core, Studio 2019 transforms how new users get up and running and helps experienced users make the most of the powerful features.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search