|While Dashing_star's suggestion will work, you will have to use the Autofill handle each time you change the Day name in your first cell. If you are looking for a formula method to automatically update your list, try this:|
1 - Enter a full Day name in A1 e.g. Sunday, Monday, etc
2 - Enter this formula in B1:
3 - Depending on how B1 is formatted, that formula will probably return a Date, not a Day name. To fix that, Format the cell as Custom - and enter dddd in the Type box. The cell should now show the Day name that follows the Day name in A1.
4 - In C1 enter this formula and drag it as far you need:
Once again, you may have to format C1 and beyond as Custom - dddd
Now, each time you change the Day name in A1, the list in B1 and beyond will automatically update.
The reason this works is because even though the Day name in A1 is Text, the formula in B1 actually returns a Date. Since Excel sees the value in B1 as a Date (even if it is formatted as a Day name) you can add one to it to get the next Date. Further, since Excel knows what day of the week each Date is, formatting the cells as Custom - dddd will return the Day names.
Note: You might want to consider using a Data Validation Drop Down in A1 with a list of Day names to choose from. This will eliminate the chance of a typographical error. If the Day name in A1 is not spelled correctly, the CHOOSE function in B1 will fail and all of the "Day name" cells will display an error.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03