+ Reply to Thread
Results 1 to 7 of 7

Formula to return sheet name not formatted as Date

  1. #1
    Valued Forum Contributor scaffdog845's Avatar
    Join Date
    02-01-2008
    Location
    Aston, PA. USA.
    MS-Off Ver
    Microsoft 365
    Posts
    373

    Formula to return sheet name not formatted as Date

    Morning all,

    I have the following formula in cell B1 to return the Sheet Name as the cell value
    Please Login or Register  to view this content.
    which returns the correct value however I can not format it as a date. Each sheet in the workbook represents a workday with the naming convention 1-13, 1-14 etc.

    How can I modify the formula to take the sheet name (1-13) and have it show in the cell as Wednesday, January 13, 2010?

    Many thanks
    Last edited by scaffdog845; 01-13-2010 at 09:49 AM. Reason: Solved
    Click here to read the Forum Rules
    Whatever it is in life you decide to go after, go after with great ferocity.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to return sheet name not formatted as Date

    Try encasing within:

    Please Login or Register  to view this content.
    format resulting cell accordingly (will be a date value)

  3. #3
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Formula to return sheet name not formatted as Date

    or if that gives you Jan 1st 2013,

    =DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename"),1)+1,255)&"-"&2010)
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to return sheet name not formatted as Date

    Quote Originally Posted by sweep
    ...if that gives you Jan 1st 2013
    would only happen if scaffdog was running non-US date format as default... I thought safe to presume not so.

  5. #5
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Formula to return sheet name not formatted as Date

    Agreed - however, stranger things have happened.

  6. #6
    Valued Forum Contributor scaffdog845's Avatar
    Join Date
    02-01-2008
    Location
    Aston, PA. USA.
    MS-Off Ver
    Microsoft 365
    Posts
    373

    Re: Formula to return sheet name not formatted as Date

    Guys encasing the formula as DO suggests
    Please Login or Register  to view this content.
    and using sweep's
    Please Login or Register  to view this content.
    both return a #Value! error.

    DO's assumption on my using US Date format is correct.

    Cells are formated as Date:*Wednesday, March 14,2001

    I'm trying this in a new workbook before using it on anything in production.

  7. #7
    Valued Forum Contributor scaffdog845's Avatar
    Join Date
    02-01-2008
    Location
    Aston, PA. USA.
    MS-Off Ver
    Microsoft 365
    Posts
    373

    Re: Formula to return sheet name not formatted as Date

    Guys....

    Both of your last 2 suggestions work. Before my first post, my formula was in a production book. After posting, I moved it to a new blank workbook and formatted the cell as a date, however I did not rename Sheet1 to a date. As soon as I did the result was as expexted.

    Sorry for the additional post assuming it did not work.

+ 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