21 Apr 2016

Excel - Calculate week days from dates

Often in Excel dates are used. In this article we'll see how we can list the corresponding day of the week using a formula. We'll use the CHOOSE() and WEEKDAY() functions, here's how:

=CHOOSE(WEEKDAY(A2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Using the above formula you'd need to have a date in cell A2, the WEEKDAY() function will pass the number of the day of the week to the CHOOSE() function and it displays the corresponding text (in numerical order). The following screen shot shows the formula in action.



This was tested in Excel 2013 but it is likely to work in any version of Excel.



Update 9th May 2016
If the date is missing it'll be zero, the result of the above function in column B will be "Saturday" - which makes no sense. To improve the above formula so if there are any missing dates the Day column shows nothing, use the following:

=IF(A2="","",CHOOSE(WEEKDAY(A2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"))

The above, in English words means, if the A2 cell is empty do make the cell (Day cell) empty. But if there's something in A2, calculate the day and display it (in the Day cell).

No comments: