Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates & Days (all)

    Is there a formula or function in excel to get the day i.e. Monday, Tuesday, Wednesday etc from a date e.g. 1/2/2003
    or more specifically just a way to check that a certain date e.g. 31/12/2002 is a Monday?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dates & Days (all)

    Try the following:

    5/26/2003 is entered in field D3.

    =TEXT(D3,"dddd") returns Monday.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates & Days (all)

    This is kinda kludgy and is off the top of my head but you could work with a nested IF statement in combination with the WEEKDAY function.
    =IF(WEEKDAY(A1,1)=1,"Sunday",IF(WEEKDAY(A1,1)=2,"M onday",IF(WEEKDAY(A1,1)=3,"Tuesday",IF(WEEKDAY(A1, 1)=4,"Wednesday",IF(WEEKDAY(A1,1)=5,"Thursday",IF( WEEKDAY(A1,1)=6,"Friday",IF(WEEKDAY(A1,1)=7,"Satur day")))))))

    I am sure that some of the Woody's experts will come up with something cleaner and more elegant.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates & Days (all)

    Before I even posted my mess there was a simple and clean way of accomplishing your task. This place is awesome!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates & Days (all)

    The WEEKDAY function returns a number that corresponds to the day of the week. If cell A1 contains a date, the formula =WEEKDAY(A1) returns
    1 = Sunday
    2 = Monday
    ...
    7 = Saturday.
    To test if the date in A1 is a Monday, use =(WEEKDAY(A1)=2)

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dates & Days (all)

    How about just custom formatting the cell where the date is held to read "dddd". Or

    Say cell A1 contains the date part e.g. 1/2/2003, make cell B1 have the formula =A1 and then format cell B1 as above. Then you get the date and the day next to each other, you can use the fill handle to copy the formula down the column of dates.
    Jerry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dates & Days (all)

    Instead making an extra cell if you want day and date, just format the DATE cell to display both:
    Format - cells - custom (no quotes):
    "dddd, mmmm d, yyyy"
    to give something like
    Friday, May 2, 2003

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dates & Days (all)

    Thanks Steve

    Even better, I'm going to try that in the future, as you say, no extra cell....neat and tidy

    Jerry
    Jerry

Posting Permissions

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