Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: DAYS OF THE WEEK

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hello verluc

    you have to format the cells this way

    dddd



    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default


    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    New Member
    Join Date
    Mar 2002
    Location
    San Ramon CA
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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