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:

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.

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:

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

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.

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