+ Reply to Thread
Results 1 to 4 of 4

Date format of just "st" "nd" "rd" and "th" with text included

  1. #1
    Registered User
    Join Date
    05-16-2011
    Location
    L.A, USA... Sorry world.
    MS-Off Ver
    Excel 2007
    Posts
    7

    Date format of just "st" "nd" "rd" and "th" with text included

    Hi everyone.

    This page has what I needed... The Date format ending with "st" "nd" "rd" and "th".

    However, I was hoping there was a way to add "Week of" to the beginning of this date format with the year at the end?

    Example of what I need for my dates (And would be so grateful if anyone could help):

    "Week of August 1st, 2013"

    Thank you for all your help
    Last edited by jeffreybrown; 08-10-2013 at 04:36 PM. Reason: Thread moved to its own thread with a reference back to the original

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Date format of just "st" "nd" "rd" and "th"

    Well, amongst many possibilities, you could adapt daddylonglegs' solution, assuming your day is in A1 and year in B1:

    ="Week of "&TEXT(A1,"mmmm d")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&", "&B1

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Date format of just "st" "nd" "rd" and "th" with text included

    With a date in A1:

    ="Week of " & TEXT(A1,"mmmm") & " " & DAY(A1) & MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2) & " " & YEAR(A1)
    Gary's Student

  4. #4
    Registered User
    Join Date
    05-16-2011
    Location
    L.A, USA... Sorry world.
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Date format of just "st" "nd" "rd" and "th" with text included

    Geniuses Man...

    Hahaa. Every time.

    Thank you both. They both worked great.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return "green", "yellow" or "red" from date/age and priority ranking
    By Cantaloop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2013, 12:12 AM
  2. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM
  3. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  4. Replies: 5
    Last Post: 06-26-2006, 09:23 PM
  5. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

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