Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Referencing Worksheet Tabs.
by Allen Wyatt
(last updated March 27, 2020)
Myrna asked if there was a way to use the information in a worksheet tab within a cell. In particular, she named her tabs using dates, and wants to use those dates within the worksheet itself.
There are two ways to go about this. If the names of your worksheet tabs consist only of dates (no other text in them), then you can use the following Excel formula to extract the date:
This works because =CELL("filename") function returns the complete path and name of the current file along with the text on the worksheet tab. The filename itself appears in square brackets. The formula finds the position of the closing bracket and extracts the first eight characters from that position to the end. (Dates can be expressed in a maximum of 10 characters, as in 12-31-2020.)
One caveat with using this formula is that it only returns anything of value if you first save the workbook. If you use it in a brand-new, unsaved workbook, it will return a #VALUE error.
Another approach that is very appealing, particularly if you have additional text in the worksheet tab, is to create a user-defined function. For instance, let's assume that your worksheet tabs have the name "Month Ending 10-31-20". In this case, you could use a function such as the following:
Function SheetName() As Date Dim sTab As String Application.Volatile sTab = ActiveSheet.Name sTab = Trim(Right(sTab, 8)) SheetName = CDate(sTab) End Function
To use this function in your worksheet, you simply enter the following in a cell:
The function returns a date serial number, so you will need to format the cell using one of the available date formats. The function works because it assumes that the date is the last 8 characters of the text in the worksheet tab. If your worksheet tabs use a different naming convention (such as placing the date at the beginning of the tab or using 10 digits for the date), then all you need to do is pull the name apart differently in the macro.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6145) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Referencing Worksheet Tabs.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in ...Discover More
Excel makes it relatively easy to copy worksheets to a different workbook. That doesn't mean it couldn't be made simpler ...Discover More
Need to create a large number of worksheets using specific names? If so, you'll love the ideas presented in this tip.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.