DAYS OF THE WEEK

[verluc]

I have a sheet with the dates of a month.
Ex.Month JANUARY 2002

1 2 3 4 5 6 7 8 9 10 11 ..... 31

How can I get the day of the week automaticly:

Ex.Mont JANUARY 2002

1 2 3 4 5 6 7 8 9 10 11 ..... 31
TU WE TH FR SA SU MO TU WE TH FR ..... TH

Thanks for help.

[IML]

I made some assumptions -
Month in A1
Year in B1
days in row a1-???

Use the formula
=TEXT(($A$1&" "&A2&", "&$B$1)+0,"dddd")
in A3 and copy it accross. If need be, you can wrap an iserror statement around this to avoid errors in months with less than 31 days.

EDITED FORMULA -

you could use
=LEFT(TEXT(($A$1&" "&A2&", "&$B$1)+0,"ddd"),2)

for the two digit code you are looking for.

[ This Message was edited by: IML on 2002-05-04 06:49 ]

[sen_edp]

hello verluc

you have to format the cells this way

dddd

[verluc]

On 2002-05-04 06:47, IML wrote:
I made some assumptions -
Month in A1
Year in B1
days in row a1-???

Use the formula
=TEXT(($A$1&" "&A2&", "&$B$1)+0,"dddd")
in A3 and copy it accross. If need be, you can wrap an iserror statement around this to avoid errors in months with less than 31 days.

EDITED FORMULA -

you could use
=LEFT(TEXT(($A$1&" "&A2&", "&$B$1)+0,"ddd"),2)

for the two digit code you are looking for.

[ This Message was edited by: IML on 2002-05-04 06:49 ]

Thay's not what I mean.
I have in cell C3 : january 2002
I have in cell D7 : 1
Now those two elements give me 01.01.2002
IN want to know what 01.01.2002 is : TUESDAY
en so on untill the last day of the month.
Thank you in advance.

[Aladin Akyurek]

On 2002-05-04 07:20, verluc wrote:

Thay's not what I mean.
I have in cell C3 : january 2002
I have in cell D7 : 1
Now those two elements give me 01.01.2002
IN want to know what 01.01.2002 is : TUESDAY
en so on untill the last day of the month.
Thank you in advance.

What is the result of:

=ISNUMBER(C3)


And, you state: Now those two elements give me 01.01.2002

You already have computed 01.01.2002 in some cell? If so, what is the result of:

=ISNUMBER(the-cell-that-contains-01.01.2002)



[ This Message was edited by: Aladin Akyurek on 2002-05-04 07:37 ]

[verluc]

On 2002-05-04 07:31, Aladin Akyurek wrote:

What is the result of:

=ISNUMBER(C3)


And, you state: Now those two elements give me 01.01.2002

You already have computed 01.01.2002 in some cell? If so, what is the result of:

=ISNUMBER(the-cell-that-contains-01.01.2002)



[ This Message was edited by: Aladin Akyurek on 2002-05-04 07:37 ]

I think you are misunderstand.
I have a row : cell A1 to A31 with entered the days of the month JANUARY 2002
In the row under this row I want the days of the week : MO TU WE TU FR SA SO
I hope,now you understand it.
Thansk for help

[Aladin Akyurek]

I think you are misunderstand.
I have a row : cell A1 to A31 with entered the days of the month JANUARY 2002
In the row under this row I want the days of the week : MO TU WE TU FR SA SO
I hope,now you understand it.
Thansk for help


Verluc,

You need to pick up some Excelese.

I have a row : cell A1 to A31 with entered the days of the month JANUARY 2002

is not a row: A1 to A31 is a range in column A.

Now, try to state precisely what you have in A1:

Is it JANUARY 2002? If so,

in B1 enter:

=ISNUMBER(A1)

What is the result?

And, in C1 enter:

=A1+0

What is the result?

Aladin

[IML]

If the result of =isnumber(c3) is true, put
=IF(MONTH($C$3)=MONTH(DATE(YEAR($C$3),MONTH($C$3),D7)),LEFT(TEXT(DATE(YEAR($C$3),MONTH($C$3),D7),"ddd"),2),"")

next to your 1 and copy it 30 add'l rows or columns.

If it false, put
=IF(ISERR((LEFT($C$3,FIND(" ",$C$3)-1)&" "&D7&", "&RIGHT($C$3,LEN($C$3)-FIND(" ",$C$3)))+0),"",LEFT(TEXT((LEFT($C$3,FIND(" ",$C$3)-1)&" "&D7&", "&RIGHT($C$3,LEN($C$3)-FIND(" ",$C$3)))+0,"ddd"),2))

next to your 1 and copy it 30 add'l rows or columns.

[g_erhard]

Given an Excel date, the Weekday function gives you a numeric result from 1 to 7, 1 being the first day of the week (you may have to set a flag of some sort if your week starts with Monday).

The Datevalue function will cook up an Excel date value, given a string that follows a Date format. I believe that it uses the Windows regional settings for dates, so if you're using MM/DD/YY (US) in Windows, Excel will interpret it properly. Likewise if Windows uses the DD/MM/YY format (Canada and Europe), Excel should also.

If you're using strings to describe your date "parts" (february, the year "two-thousand three" AD), I'd consider changing things to numbers and then using formatting to change the display...

B1 = 2 (month) date format = "MMMM"
B2 = 17 (day)
B3 = 1967 (year)

equation: Weekday(Datevalue(B1 & "/" & B2 & "/" & B3))

Results = 6 (date format "DDDD" = "Friday")
------------------
SO, to solve Verluc''s problem, all he really needs is the first date on the worksheet, and then add 1 to each cell going across. The dates will populate from one month to the next automatically. Then use the Month, Day, and Weekday functions to display details.


[ This Message was edited by: g_erhard on 2002-05-06 07:32 ]

[verluc]

On 2002-05-06 07:26, g_erhard wrote:
Given an Excel date, the Weekday function gives you a numeric result from 1 to 7, 1 being the first day of the week (you may have to set a flag of some sort if your week starts with Monday).

The Datevalue function will cook up an Excel date value, given a string that follows a Date format. I believe that it uses the Windows regional settings for dates, so if you're using MM/DD/YY (US) in Windows, Excel will interpret it properly. Likewise if Windows uses the DD/MM/YY format (Canada and Europe), Excel should also.

If you're using strings to describe your date "parts" (february, the year "two-thousand three" AD), I'd consider changing things to numbers and then using formatting to change the display...

B1 = 2 (month) date format = "MMMM"
B2 = 17 (day)
B3 = 1967 (year)

equation: Weekday(Datevalue(B1 & "/" & B2 & "/" & B3))

Results = 6 (date format "DDDD" = "Friday")
------------------
SO, to solve Verluc''s problem, all he really needs is the first date on the worksheet, and then add 1 to each cell going across. The dates will populate from one month to the next automatically. Then use the Month, Day, and Weekday functions to display details.


[ This Message was edited by: g_erhard on 2002-05-06 07:32 ]

Hi,
Can you give me more information how to insert this formule.
Many thanks