=MID to DATEVALUE

[prw79]

Hello all,

I have a cell that is automatically named after its sheet name with the following formula: =MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255)
The sheet name is named after a date like "vr-2-sep".
However excel doesn't recognize it as a date. So i would like to copy the result into another cell and format it as a date value so i can link it with other date values somewhere else in the worksheet.

hope someone has a fix for this, thanks

 

[6StringJazzer]

What language are you using? I do not recognize "vr-2-sep" as a date. What date is that supposed to be?

The DATEVALUE function will convert a text string to a date, but it must be in a format that Excel can recognize. Otherwise you will need a more customized function.

 

[prw79]

What language are you using? I do not recognize "vr-2-sep" as a date. What date is that supposed to be?

The DATEVALUE function will convert a text string to a date, but it must be in a format that Excel can recognize. Otherwise you will need a more customized function.

it is friday september the 2nd in Dutch, but excel is English. The sheetname does not accept / or : signs, that's why it is in that format

 

[Dave Patton]

The double negative coerces the text to a number; the result is formatted as a date.

Date and Time 3.xlsm
	A	B	C
1
2	vr-2-sep	2-Sep-22	2-Sep-22
3	vr-12-sep	12-Sep-22	12-Sep-22
4
4e

Cell Formulas
Range	Formula
B2:B3	B2	=--(MID(A2,FIND("-",A2)+1,99))
C2:C3	C2	=DATEVALUE(MID(A2,FIND("-",A2)+1,99))

 

[prw79]

The double negative coerces the text to a number; the result is formatted as a date.

Date and Time 3.xlsm
	A	B	C
1
2	vr-2-sep	2-Sep-22	2-Sep-22
3	vr-12-sep	12-Sep-22	12-Sep-22
4
4e

Cell Formulas
Range	Formula
B2:B3	B2	=--(MID(A2,FIND("-",A2)+1,99))
C2:C3	C2	=DATEVALUE(MID(A2,FIND("-",A2)+1,99))

This works perfectly! many thanks

 

[Dave Patton]

Thanks for the feedback.