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: 21:47
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  Identity Verified
France
Local time: 03:47
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: 20:47
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 Trados Studio 2017 only €435 / $519
Get the cheapest prices for SDL Trados Studio 2017 on ProZ.com

Join this translator’s group buy brought to you by ProZ.com and buy SDL Trados Studio 2017 Freelance for only €435 / $519 / £345 / ¥63000 You will also receive FREE access to Studio 2019 when released.

More info »
TM-Town
Manage your TMs and Terms ... and boost your translation business

Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.

More info »



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