Numerical Dates to Days

Question

I have a column of dates in the format 7/1/2017, which I need to turn into days of the week. I followed the following procedure, and no change occurred.

Select the cells that contain dates that you want to show as the days of the week.
On the Home tab, in the Number group, click the arrow, click More Number Formats, and then click the Number tab.
Under Category, click Custom, and in the Type box, type dddd for the full name of the day of the week (Monday, Tuesday, and so on), or ddd for the abbreviated name of the day of the week (Mon, Tue, Wed, and so on).

I am using Excel for Mac, version 15.33 (170403)

Is there a different procedure, or a formula I can apply to the column of dates?

sergeibaklan · Answer

Hi Daniel,&nbsp;It depends on your needs. If you'd like only to show your dates as weekdays that's what you did.&nbsp;If you'd like to receive weekdays numbers to perform with them some calculations use WEEKDAY function.&nbsp;If you'd like to have the text with weekdays names use TEXT function where second parameter will be as in your custom formatting.

daniel leavey · Answer

There is a column of numbers, formatted as dates like 7/21/17. The second column, with the heading, Days, has blank cells. The instructor copied the cells of column 1 into column 2, then applied a Text Function to change the numbers in column 2 to the days of the week, from top to bottom.

I tried using his formula, then the Formula builder, and finally, the step-by-step procedure I picked up from Microsoft support outlined in my first message. None of the routines worked.

I have attached the Excel file.

LEMONADE.xlsx47 KB

daniel leavey · Answer

Maybe my copy of Excel, or the Mac version simply doesn't work the way all the solutions I've found have described. For example, I found a solution at this URL:

https://www.extendoffice.com/documents/excel/814-excel-change-date-to-day-of-week.html

After selecting a column of dates, I couldn't bring up a context menue by right clicking on the column.

Does anyone here have experience working with Excel 15.33 on a Mac?

sergeibaklan · Answer

Hi Daniel,&nbsp;I have no Mac version, but all such transformations shall work the same way on all platforms.&nbsp;It looks like in your first column (Date) dates are entered as text, not as numbers (in Excel dates are numbers where by default 1 is Jan 01, 1900 for Windows and Jan 01, 1904 for Mac). Applying formatting you may receive different representation of such numbers (as full dates, weekdays, etc.).&nbsp;When you enter into the cell the date in your default regional format Excel automatically applies date format for such cell (if you didn't force another one).&nbsp;You may see visually do you have text or number/date in your cell - in first case values arealigned to the left side of the cell, in second one - to the right. Another simple test in any empty cell enter the formula =&lt;cell with date&gt;+1. If the result an error when you have text.&nbsp;Please check the second sheet in the attached file if it works on your Mac.

vijaykumar shetye · Answer

Dear Daniel Leavey,If the issue has not been resolved as yet, then can you try one of the following formulas.=WEEKDAY(VALUE(A2),1) or=WEEKDAY(DATEVALUE(A2),1)and let me know what result you get.Change the 'return type' of the weekday as required.&nbsp;Vijaykumar Shetye,Spreadsheet Excellence,Panaji, Goa, India&nbsp;

Forum Discussion

Numerical Dates to Days

5 Replies

Resources