Excel format date

[camioneur]

Is it possible to format a cell depending on whether the date in a cell is a weekday or a weekend day?

[kempie]

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

[camioneur]

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?

[kempie]

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).

[camioneur]

Wow, thanks Kempie, I'm on it. Will ley tou know

[camioneur]

sorry kempie, you've beaten me. if i email you my spreadsheet can you sort the date column for me? when i do it, it removes the date in the cell and i want it to highlight the date in a cell if it falls at the weekend. maybe that can't be done?

[kempie]

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.

[camioneur]

Got it kempie thank you. The reason it wouldn't work for me, the date column was formatted in "custom" to show just days and months and not "date" It's all working fine now.

Thanks again for your assistance and patience