MS Excel question

I've got a LARGE spreadsheet that has a column of dates. The dates are formatted like 02/01/2005 (today's date). I need to add a column beside this one and have that one automatically calculate the day of the week these days have fallen on. The dates are not sequential either.

How?

ok, why would you want the day of the week in a separate column? The one of the options for date formatting in a cell is, for example "Tuesday, March 1, 2005" where it includes the day of the week automatically.

Is that not OK ?

Use this in the cell you want the day of the week to appear:
=text(a1,"dddd")
where "a1" is the cell with the date in it
using dddd (4 "d") will return the full name of the day (Wednesday for example)
using ddd (3 "d") will return the abreviated name of the day (Tue or Fri for example)
use the "Fill Down" to do the rest of the cells.
This shouldnt take more than 10 or 15 seconds to do.

Thanks for the question and the answers.

I found this very useful as well.

Greg

Hello Turbodog,

I just thought I would point out that today's date is actually 03/01/2005 /ubbthreads/images/graemlins/smile.gif and its Tuesday, if that helps...

Tom

[ QUOTE ]
AlphaTea said:
Use this in the cell you want the day of the week to appear:
=text(a1,"dddd")
where "a1" is the cell with the date in it
using dddd (4 "d") will return the full name of the day (Wednesday for example)
using ddd (3 "d") will return the abreviated name of the day (Tue or Fri for example)


[/ QUOTE ]

If I used =text(a1,"mmmm"), would I get the month only (or "yyyy" for year)?

[ QUOTE ]
greenLED said:
If I used =text(a1,"mmmm"), would I get the month only (or "yyyy" for year)?

[/ QUOTE ]

Yes

[ QUOTE ]
capnal said:
ok, why would you want the day of the week in a separate column? The one of the options for date formatting in a cell is, for example "Tuesday, March 1, 2005" where it includes the day of the week automatically.

Is that not OK ?

[/ QUOTE ]

Well, I could then more easily compare certain days of the week to data in other fields. Or at least that's my plan.

Now I just need to be able to color certain days different colors.

[ QUOTE ]
SilverFox said:
Hello Turbodog,

I just thought I would point out that today's date is actually 03/01/2005 /ubbthreads/images/graemlins/smile.gif and its Tuesday, if that helps...

Tom

[/ QUOTE ]

Every little bit helps.

[ QUOTE ]
turbodog said:
[ QUOTE ]
SilverFox said:
Hello Turbodog,

I just thought I would point out that today's date is actually 03/01/2005 /ubbthreads/images/graemlins/smile.gif and its Tuesday, if that helps...

Tom

[/ QUOTE ]

Every little bit helps.

[/ QUOTE ]

Don't be too sure. I think many CPF members would say it's 1/3/2005

Maybe that's the "metric" date.

And according to the Mayan calendar it's ... oh never mind.

[ QUOTE ]
turbodog said:
Now I just need to be able to color certain days different colors.

[/ QUOTE ]

Well, you might be able to do it with conditional formatting. ON my version, you can only set three conditions, so you'd only be able to color 3 of the 7 days. Go to the first cell, click on Format, Conditional Formatting. Change "Between" to "Equal to" and put "Monday" next to it. Then click on "Format..." and set the attributes (color, font, whatever). Then click "add>>" for another day, etc. Then, when you're done and press OK, with your cursor still on the first cell, press Ctrl+C to copy, Ctrl+Space to hilight the entire column, then go up and click on "Edit->Paste Special..." and select "Formats" then OK.

Anyone have a better idea?

what lymph said. What's up with Excel and only allowing three fields for "stuff". Data sort has the same shortcoming - not enough fields available to sort on.

Is this an ongoing spreadsheet, or a static one? If it's static, sort it by day of the week, add your colors, and resort by the original way.

ongoing

I think I've got it handled now. I found some colored highlighters that will mark on glass. That way I can color it right on the monitor screen!