Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    datevalue (excel 2007)

    How can this formula be modified to return the complete day name versus the abbreviated name?

    =DATEVALUE(B1&"-"&$A$1&"-"&$C$1)

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

    Re: datevalue (excel 2007)

    Select the cell(s) with the formula.
    Select Format | Cells...
    Activate the Number tab if necessary.
    Select Date in the list of categories.
    Choose an appropriate date format in the list on the right.
    If you don't see the format you want, select Custom in the list on the left, and specify a custom format in the Type box.
    Finally click OK.

  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datevalue (excel 2007)

    Hi Hans,
    Thanks for your help.
    I can find no format which returns only the day name, example :"Monday". I'm not clear on why the formula returns "Mon" I can find no formatting for that either. My goal is to get this formula to return the complete name for the given day.

  4. #4
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datevalue (excel 2007)

    Hi Hans,

    Would you please explain for me how this formula returns the abbreviated day? "=DATEVALUE(A10&"-"&$B$3&"-"&$D$3)" I would really like to understand it. Your help is appreciated.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: datevalue (excel 2007)

    As Hans has said, it's a formatting issue. The formula returns a date, the cell format determines what is displayed to you.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: datevalue (excel 2007)

    The DATEVALUE function takes a text string that represents a date and turns it into an Excel date. A date in Excel is actually a number, to be precise the number of days since December 31, 1899 (*). So for example January 12, 1900 is stored as the number 12. Today (February 18, 2008) is stored as 39496. Excel can display a date in all kinds of different ways - there are several built-in date formats and you can create a huge number of custom date formats. It is important to keep in mind that these date formats don't change the way the date is stored in Excel, the underlying value is always a number; the date format only determines what the date looks like on your computer screen and in the printed output.
    So if the cell with your DATEVALUE formula shows the abbreviated name of the day, that is just the way it is formatted. By changing the format in the Number tab of Format | Cells..., you can make it look differently.
    Why did you see this format initially? Either because the cell was already formatted that way, or (less likely) by accident.

    (*) There is a bit more to it than that, but I won't go into that here, it's not relevant for this discussion.

  7. #7
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datevalue (excel 2007)

    Thanks Hans and Rory.
    I think I'm finally starting to get it.
    Hans this formula was created by someone at "The Lounge" to assit me in on a previous project.
    I appreciate all the help you folks are so kind to provide. Have a great day!

  8. #8
    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: datevalue (excel 2007)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>It is leading zero, not trailing zero. We want the trailing zero, otherwise 10, 20, 30 would be 1, 2, 3 ...

    Steve

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

    Re: datevalue (excel 2007)

    Of course, thanks! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I will edit my reply.

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

    Re: datevalue (excel 2007)

    Edited by HansV to change trailing to leading - thanks, Steve!

    If you want to see only the full name of the day, select Custom in the list of categories, and enter dddd in the Type box.
    In custom formats, you can use:

    d = day number without leading zero, 1-31
    dd = day number with leading zero, 01-31
    ddd = abbreviated name of day, Sun-Sat
    dddd = full name of day, Sunday-Saturday

    m = month number without leading zero, 1-12
    mm = month number with leading zero, 01-12
    mmm = abbreviated name of month, Jan-Dec
    mmmm = full name of month, January-December

    yy = two digit year, 08
    yyyy = four digit year, 2008

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: datevalue (excel 2007)

    Hi Bill,

    Another way to get the formula to return the day, would be to embed your existing formula in a TEXT formula with the appropriate formatting switch. For example:
    =TEXT(DATEVALUE(A10&"-"&$B$3&"-"&$D$3),"dddd, d of mmm yyyy")
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datevalue (excel 2007)

    Hi macropod,

    Thanks so much for your reply. Very helpful also.

Posting Permissions

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