date format into day of week

[Steve]

I have column in excel which show date in format 2009-08-21. Is it possible
in new column to get day of week from this data?

 

[Rick Rothstein]

Is this what you are looking for?

 

[Rick Rothstein]

Is this what you are looking for?

=TEXT(A1,"dddd")

 

[Pete_UK]

Just use:

=A1

and then format the cell using a custom format of dddd if you want the
full day or ddd if you just want the first 3 letters of the day.

An alternative:

=TEXT(A1,"ddd")

These assume your date is in A1.

Hope this helps.

Pete

 

[Michael Bednarek]

I have column in excel which show date in format 2009-08-21. Is it possible
in new column to get day of week from this data?

Asumption: source in A1.
Format the target cell to "DDD" or "DDDD"; then: =A1

Or, depending on what you want, =Weekday(A1) or

=Choose(Weekday(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
or similar.

 

[Steve]

Just use:

=A1

and then format the cell using a custom format of dddd if you want the
full day or ddd if you just want the first 3 letters of the day.

An alternative:

=TEXT(A1,"ddd")

These assume your date is in A1.



Hi Pete (and Rick/Michael),

Can you please explain me how to format source/target cell as DDDD ? Should
I type DDDD as new vaule and save it?

 

[Pete_UK]

Select the cell(s) with the formula in and click on Format | Cells |
Number tab (or just right-click on the cells), and choose Custom from
the bottom of the list then type DDDD (or dddd) in the Type box. Then
click OK.

You don't need to do this if you use the TEXT formula.

Hope this helps.

Pete

 

[Steve]

Select the cell(s) with the formula in and click on Format | Cells |
Number tab (or just right-click on the cells), and choose Custom from
the bottom of the list then type DDDD (or dddd) in the Type box. Then
click OK.



Still cannot get it. can you please type "2009-08-10" in A1 and get day
result in A2, and post that excel file here..?
Thanks.

 

[Pete_UK]

If you are putting quotes around the date then Excel will see that as
a text value and not a proper date. Ensure that A1 is formatted as a
date in the style you wish, i.e. use a custom format (as described
above) of yyyy-mm-dd, and then enter your date (without any quotes).

I can't attach an excel file to the newsgroups.

Pete

 

[JP Ronse]

Hi Steve,

Try

A1= 2009-08-10 (cell should be formatted as yyyy/mm/dd)
A2=weekday(A1) (cell should be formatted as "ddd" od "dddd", custom format)

Wkr,


JP

 

[Steve]

Great, that's it!!
Thanks man.

Hi Steve,

Try

A1= 2009-08-10 (cell should be formatted as yyyy/mm/dd)
A2=weekday(A1) (cell should be formatted as "ddd" od "dddd", custom
format)

Wkr,


JP

 

[Steve]

Hi Steve,
Hi,

last thing - is it possible in A3 get week number, for example:
A1 0 2009-08-24
A2=weekday(A1)=Monday
A3=W(xy) where XY is number of week in this year?

 

[JP Ronse]

Hi Steve,
A3: =weeknum(A1,type)

type=1 if week starts on Sunday, 2 if week starts on Monday.

But know that there exist different approaches to define the week number. Si
I recommend Chip Pearson 's page http://www.cpearson.com/excel/weeknum.htm
to get more info about this.

Wkr,

JP

 

[Steve]

Hi Steve,
A3: =weeknum(A1,type)

type=1 if week starts on Sunday, 2 if week starts on Monday.

But know that there exist different approaches to define the week number.
Si I recommend Chip Pearson 's page
http://www.cpearson.com/excel/weeknum.htm to get more info about this.

Resolved like:
=1+INT((A1-DATE(YEAR(A1);1;2)+WEEKDAY(DATE(YEAR(A1);1))/7)