+ Reply to Thread
Results 1 to 11 of 11

Matching date with days

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    Karachi, PAkistan
    MS-Off Ver
    Excel 2003
    Posts
    15

    Matching date with days

    Is there any formula to match dates with the days in excel.
    e.g if my date is 25-jan-12 on cell A1 and i wish to return the day of the week on cell B1, what formula can be used?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,579

    Re: Matching date with days

    Depends what you mean by day of the week. There is a WEEKDAY function, or, if you just mean Monday, Tuesday, etc, you could use formatting on a copy of the cell.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Matching date with days

    How about this:

    =TEXT(WEEKDAY(A1),"dddd")

    - Moo

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,579

    Re: Matching date with days

    @Moo: that would probably DAY not WEEKDAY but, if it's just for display, you can do that with formatting.


    Regards, TMS

  5. #5
    Registered User
    Join Date
    11-28-2012
    Location
    Karachi, PAkistan
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Matching date with days

    It worked perfectly guys! thanks alot

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,579

    Re: Matching date with days

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,579

    Re: Matching date with days

    Just to clarify some of the options:

    HTML Code: 
    Value		Formula			Format			Type			General
    
    28/11/2012	=TODAY()								
    				
    28		=A1			format "dd"		numeric			41241
    				
    Wed		=A1			format "ddd"		(numeric)		41241
    				
    Wednesday	=A1			format "dddd"		(numeric)		41241
    				
    28		=TEXT(A1,"dd")					text (left aligned)	28
    				
    28		=DAY(A1)					numeric			28
    				
    28		=TEXT(DAY(A1),"00")				text (left aligned)	28
    				
    4		=WEEKDAY(A1)								4
    				
    4		=WEEKDAY(A1,1)								4
    				
    3		=WEEKDAY(A1,2)								3
    
    2		=WEEKDAY(A1,3)								2

    WEEKDAY is the day of the week. It has a second parameter which determines which day you start counting with;

    1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
    2 Numbers 1 (Monday) through 7 (Sunday).
    3 Numbers 0 (Monday) through 6 (Sunday).


    I probably confused the issue when I replied to Moo as using TEXT would require the second parameter to be "00" not "dd".


    Regards, TMS

  8. #8
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Matching date with days

    Thanks, Trevor. Appreciate the clarification.
    - Moo

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,579

    Re: Matching date with days

    @Moo: You're welcome. Thanks for the rep.

  10. #10
    Registered User
    Join Date
    11-28-2012
    Location
    Karachi, PAkistan
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Matching date with days

    @TMS Thanks for a detail u sent me for the thread. I'm trying to apply it. i hope i succeed

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,579

    Re: Matching date with days

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1