0

I'm using this code to grab the sheet name and put it into cell C2.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

I would like cell C1 to display the correct day of the week (ddd) depending on the date set in cell C2 / Sheet name

Adding =C2 into C1 and changing the cell format to "custom" "ddd" does not work. It just displays the date as it's written on the sheet name.

Ideas?

  • 1
    =Text(weekday(C2,1),"dddd") This formula will display the name of the weekday, Weekday(C2,1) will extract the day number starting from Sunday as 1 and Text will convert it to full day name – Sam Dec 15 '16 at 9:14
  • What is the date value in C2? – Branislav Kollár Dec 15 '16 at 17:20
0

It sounds like the "DATE" you are pulling from C2 is actualy a TEXT.
If you select the cell C2 with this "DATE" and apply "General" formatting, what do you see?

  • Nothing changed >> Text
  • Whole number >> Date value
  • Decimal number less than or equal to 1 >> Time value
  • Decimal number something like 42719,7724475694 >> Date and Time value

Convert Date stored as Text to Date (serial number)

To convert a Text/String to Date, you can use the DATEVALUE function.

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008.

If you then use "ddd" number format, you should see the name of the day.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.