| Home |
| Search |
| Today's Posts |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date from sheet name
Yes. Very nice and much neater.
"Steve Dunn" wrote: Had a Doh! moment, just before bed... =IF((LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))5, MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,8), DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+4,2)& MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3))) much neater, same result. Goodnight. "Steve Dunn" wrote in message ... Here we go (another monster): =IF((LEN(CELL("filename"))-FIND("]",CELL("filename")))5, RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]", CELL("filename"))),DATEVALUE(RIGHT(CELL("filename" ), LEN(CELL("filename"))-FIND("]",CELL("filename"))-3)&" "& LEFT(RIGHT(CELL("filename"),LEN(CELL("filename"))- FIND("]",CELL("filename"))),3)&" "&YEAR(TODAY()))) Month name must always be 3 letters, but day numbers can be 1 or 2 digits. HTH Steve D. "Steve Dunn" wrote in message ... Another possibility. This one relies on sheet names always having two digits for the day(s) of the month, and the month always being 3 letters. e.g. Jan01, Jan01-02 =IF(LEFT(RIGHT(CELL("filename"),3),1)="-",RIGHT(CELL("filename"),8), DATEVALUE(RIGHT(CELL("filename"),2)&" "& LEFT(RIGHT(CELL("filename"),5),3)&" "&YEAR(TODAY()))) I'll play around with it a bit to allow for single digit days. "JBoulton" wrote in message ... I'd like to turn a sheet name into a date. Apr28 to 4/28/10 (as a date) and Apr28-30 to Apr28-30 (as text) TIA and thanks for any ideas. Jim |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date as Sheet Name | Excel Discussion (Misc queries) | |||
| macro to: Add new sheet, then rename new sheet with todays date | Excel Worksheet Functions | |||
| Rename sheet with NOW date | Excel Worksheet Functions | |||
| copying date from one sheet to another work sheet | Excel Worksheet Functions | |||
| how to use sumif function to check date in 1 sheet is < 2 sheet | Excel Worksheet Functions | |||
Threaded Mode
