Quizzes & Puzzles6 mins ago
Excel format date
Is it possible to format a cell depending on whether the date in a cell is a weekday or a weekend day?
Answers
1 to 7 of 7
Best Answer
No best answer has yet been selected by camioneur. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
Yes. You can use conditional formatting to set the style for the cell. If you are unfamiliar with conditional formatting, Excel Help will guide you through the process.
Using the formula
=WEEKDAY(A1,2)>5
(where A1 represents the cell to be formatted) you can select the style of format for when the condition is true (i.e. is the weekend).
If you prefer, you can adapt the above formula to change the format of weekdays by replacing >5 with <6
Using the formula
=WEEKDAY(A1,2)>5
(where A1 represents the cell to be formatted) you can select the style of format for when the condition is true (i.e. is the weekend).
If you prefer, you can adapt the above formula to change the format of weekdays by replacing >5 with <6
Thanks for that Kempie but I'm not sure that I explained myself well enough.
I have a column with dates in it, not days and would like to format the cells in this column according to whether or not they fall at a weekend. Surely that would involve linking the column to a calendar of some sort rather than just a formula?
Does that make sense?
I have a column with dates in it, not days and would like to format the cells in this column according to whether or not they fall at a weekend. Surely that would involve linking the column to a calendar of some sort rather than just a formula?
Does that make sense?
The answer above is designed to work for dates.
The WEEKDAY formula looks at the date in the cell, calculates the day of the week to which it relates, and returns a value based on that day.
e.g. Monday = 1, Tuesday = 2, ... , Sunday = 7.
Thus if the date in the cell is 13/01/2007 (or 13-Jan-2007, or whichever date format you have chosen) it will calculate that it is a Saturday and return a value of 6.
In my example formula only return values above 5 change the format of the cell i.e. Saturday (6) and Sunday (7).
The WEEKDAY formula looks at the date in the cell, calculates the day of the week to which it relates, and returns a value based on that day.
e.g. Monday = 1, Tuesday = 2, ... , Sunday = 7.
Thus if the date in the cell is 13/01/2007 (or 13-Jan-2007, or whichever date format you have chosen) it will calculate that it is a Saturday and return a value of 6.
In my example formula only return values above 5 change the format of the cell i.e. Saturday (6) and Sunday (7).
Don't place the formula directly into the cell.
Select the first cell in the date column, then using the Format menu click on Conditional Formatting....
A box will pop up... under Condition 1 change Cell value is to Formula is and type the WEEKDAY formula in the input box beside it, making sure that you replace the A1 in the example with the cell reference for the date cell you selected.
Use the Format... button to select how you want the result to look then click OK to finish.
Finally, paste the format from that cell to the other cells in the date column.
If you encounter any problems let me know.
Select the first cell in the date column, then using the Format menu click on Conditional Formatting....
A box will pop up... under Condition 1 change Cell value is to Formula is and type the WEEKDAY formula in the input box beside it, making sure that you replace the A1 in the example with the cell reference for the date cell you selected.
Use the Format... button to select how you want the result to look then click OK to finish.
Finally, paste the format from that cell to the other cells in the date column.
If you encounter any problems let me know.
1 to 7 of 7
