Thanks:  0
Likes:  0

# Thread: DAYS OF THE WEEK

1. 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.

2. 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 ]

3. hello verluc

you have to format the cells this way

dddd

4. On 2002-05-04 06:47, IML wrote:
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.

5. On 2002-05-04 07:20, verluc wrote:
On 2002-05-04 06:47, IML wrote:
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.
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 ]

6. 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:
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.
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

7. 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?

8. 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.

9. 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 ]

10. 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,
Many thanks

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•